Disaster recovery 101: fixing metadata corruption without a backup

(Check out my online training courses: SQL Server: Detecting and Correcting Database Corruption and SQL Server: Advanced Corruption Recovery Techniques. We can also help you with disaster recovery.)

Yesterday on the MVP newsgroup someone was asking how to fix some metadata corruption when their backups all had the corruption in too. There are two options: export everything out to a new database or roll up your sleeves and manually fix the system tables.

Although some people may think this is scary and dangerous to do, it really isn’t that bad if you take your time. Take a database backup beforehand just in case you do the wrong thing.

The best that can happen is that you fix the unfixable corruption in a short amount of time and you’re the hero of the hour. However, you’re still going to have to perform the export at some point later – read on to find out why.

Be aware that the techniques I’m going to tell you are undocumented and unsupported. Use at your own risk.

There, that’s the scary part. I have to say that or my good friend Bob Ward (blog|twitter) won’t like me any more. Seriously though, be careful with this stuff and read the next part below because if you do this to your database, CSS may refuse to help you with further corruption.

You can download the pre-corrupted 2008 SP1 database from corrupt.zip (~1MB). If you’re on 2005, there is a set of instructions in the zip that shows you how to create the corrupt database. Just be careful that you don’t accidentally corrupt master, like I did, and then have to fix that too.

When you restore the database and run DBCC CHECKDB, you’ll see the following:

DBCC CHECKDB (corrupt) WITH NO_INFOMSGS;
GO

Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

So try DBCC CHECKCATALOG:

DBCC CHECKCATALOG (corrupt) WITH NO_INFOMSGS;
GO

Warning: The system catalog was updated directly in database ID 12, most recently at Mar 10 2011  8:09AM.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=2105058535) of row (object_id=2105058535,column_id=1) in sys.columns does not have a matching row (object_id=2105058535) in sys.objects.
Msg 3853, Level 16, State 1, Line 1
Attribute (object_id=2105058535) of row (object_id=2105058535,index_id=1) in sys.indexes does not have a matching row (object_id=2105058535) in sys.objects.
Msg 3855, Level 16, State 1, Line 1
Attribute (data_space_id=1) exists without a row (object_id=2105058535,index_id=1) in sys.indexes.
Msg 3853, Level 16, State 1, Line 1
Attribute (parent_object_id=2105058535) of row (object_id=5575058) in sys.objects does not have a matching row (object_id=2105058535) in sys.objects.

Look at what I highlighted in bold. In 2008 onwards, if you make direct modifications to the system catalogs using the techniques I describe, that fact is persisted for all time in the database and CSS will likely refuse to help you with further corruption issues.

I first blogged about the techniques for fixing this back in 2008 in this blog post. You’re going to have to manually alter the ‘hidden’ system tables. To do this you’ll need to shutdown the server, add ‘-m;‘ to the start of the startup parameter string in the Advanced tab of the SQL Server Configuration Manager, startup the server again and connect using the Dedicated Admin Connection (using ‘admin:‘ as the prefix to your connection string in SSMS or SQLCMD -A). If you’re not comfortable with any of this, walk away now.

The system tables we’ve known and loved for ever basically map to three of the hidden system tables:

• Sysindexes is sys.sysidxstats
• Sysobjects is sys.sysschobjs
• Syscolumns is sys.syscolpars

You can see that these tables exist by doing a SELECT * FROM sys.objects but you can’t select from then until you connect using the DAC, and you can’t modify them unless you’re connected with the DAC and the server is in single-user mode.

As far as the errors from DBCC CHECKCATALOG are concerned, they mean:

1. There’s an extra row in sys.syscolpars
2. There’s an extra row in sys.sysidxstats
3. There’s an extra row in sys.sysidxstats
4. There’s an extra row in sys.sysschobjs

To fix these errors, run the following code:

USE corrupt;
GO

DELETE FROM sys.syscolpars WHERE ID=2105058535;
GO
DELETE FROM sys.sysidxstats WHERE ID=2105058535;
GO
DELETE FROM sys.sysschobjs WHERE ID=5575058;
GO

Warning: System table ID 41 has been updated directly in database ID 12 and cache coherence may not have been maintained. SQL Server should be restarted.

(1 row(s) affected)
Warning: System table ID 54 has been updated directly in database ID 12 and cache coherence may not have been maintained. SQL Server should be restarted.

(1 row(s) affected)
Warning: System table ID 34 has been updated directly in database ID 12 and cache coherence may not have been maintained. SQL Server should be restarted.

(1 row(s) affected)

And then DBCC CHECKDB will come back clean, and DBCC CHECKCATALOG will be clean apart from the message about the system catalog being updated.

And there you have it – a way to quickly fix metadata corruptions before having to do the export at a later date.