Easy Way To Fix SQL Server DBCC CHECKDB Consistency Errors

I had a production database with several consistency errors (after a hardware failure and recovery of the files from the volume). After searching many solutions pointing to low level repair commands, and even trying the REPAIR_ALLOW_DATA_LOSS option (which did not work even though it is supposed to as a last resort) I came across the following simple and effective fix, which I think could fix virtual any internal problem without the need to resort to low level commands and data patchwork (and the lost time plus risk involved).

Just use the SQL Management Studio GUI to “Copy Database” but use the “SQL Management Object” method (not the default detach and attach / file copy method as that would also copy the internal errors). It basically queries the data like and end user, so it does not matter if there are internal inconsistencies as long as the good data can still be queried. Nice eh?!

It worked perfectly for me when DBCC CHECKDB REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS were unable to fix it. Also I read that even a backup and restore do not fix the internal issue. So this simple, dumb but totally effective GUI based solution is just perfect.

Hope that helps someone else searching for a solution! Enjoy. Sometimes the simplest things are the best, you just need to know when to use them, take a step back from the internals.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s