{"id":597,"date":"2011-03-10T08:11:00","date_gmt":"2011-03-10T08:11:00","guid":{"rendered":"\/blogs\/paul\/post\/Disaster-recovery-101-fixing-metadata-corruption-without-a-backup.aspx"},"modified":"2017-04-13T09:54:42","modified_gmt":"2017-04-13T16:54:42","slug":"disaster-recovery-101-fixing-metadata-corruption-without-a-backup","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/disaster-recovery-101-fixing-metadata-corruption-without-a-backup\/","title":{"rendered":"Disaster recovery 101: fixing metadata corruption without a backup"},"content":{"rendered":"

(Check out my online training courses:\u00a0SQL Server: Detecting and Correcting Database Corruption<\/a>\u00a0and\u00a0SQL Server: Advanced Corruption Recovery Techniques<\/a>. We can also\u00a0help you<\/a>\u00a0with disaster recovery.)<\/span><\/p>\n

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.<\/span><\/p>\n

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.<\/span><\/p>\n

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.<\/span><\/p>\n

Be aware that the techniques I’m going to tell you are undocumented and unsupported. Use at your own risk.<\/span><\/strong><\/p>\n

There, that’s the scary part. I have to say that or my good friend Bob Ward (<\/span>blog<\/span><\/a>|<\/span>twitter<\/span><\/a>) 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.<\/span><\/p>\n

You can download the pre-corrupted 2008 SP1 database from <\/span>corrupt.zip<\/span><\/a>\u00a0(~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.<\/span><\/p>\n

When you restore the database and run DBCC CHECKDB<\/span>, you’ll see the following:<\/span><\/p>\n

DBCC CHECKDB (corrupt) WITH NO_INFOMSGS;
\nGO<\/span><\/p>\n

Msg 0, Level 20, State 0, Line 0
\nA severe error occurred on the current command.\u00a0 The results, if any, should be discarded.<\/span><\/p><\/blockquote>\n

So try DBCC CHECKCATALOG<\/span>:<\/span><\/p>\n

DBCC CHECKCATALOG (corrupt) WITH NO_INFOMSGS;
\nGO<\/span><\/p>\n

Warning: The system catalog was updated directly in database ID 12, most recently at Mar 10 2011\u00a0 8:09AM.
\n<\/strong>Msg 3853, Level 16, State 1, Line 1
\nAttribute (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.
\nMsg 3853, Level 16, State 1, Line 1
\nAttribute (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.
\nMsg 3855, Level 16, State 1, Line 1
\nAttribute (data_space_id=1) exists without a row (object_id=2105058535,index_id=1) in sys.indexes.
\nMsg 3853, Level 16, State 1, Line 1
\nAttribute (parent_object_id=2105058535) of row (object_id=5575058) in sys.objects does not have a matching row (object_id=2105058535) in sys.objects.<\/span><\/span><\/span><\/p><\/blockquote>\n

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.<\/span><\/p>\n

I first blogged about the techniques for fixing this back in 2008 in <\/span>this blog post<\/span><\/a>. You’re going to have to manually alter the ‘hidden’ system tables. To do this you’ll need to shutdown the server, add ‘-m;<\/span>‘ 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:<\/span>‘ as the prefix to your connection string in SSMS or SQLCMD -A<\/span>). If you’re not comfortable with any of this, walk away now.<\/span><\/p>\n

The system tables we’ve known and loved for ever basically map to three of the hidden system tables:<\/span><\/p>\n