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

Your email address will not be published. Required fields are marked *