SQL Server Database Corruption, Part XI: Full Recovery Operations

In part 9 of this series on SQL Server database corruption I defined a list of key things to do when responding to database corruption. And in that list of options and operations was the mention that in some cases you may have to revert back to using a full-blown recovery operation – meaning that you’ll need to completely restore your database from scratch. Accordingly, this post provides a bit more detail on what that kind of operation looks like – and is a supplemental ‘follow-up’ post (like the post before it which showed how to do page-level restores).

Restoring Entire Databases as a Means of Recovering from Corruption

Of course, in order to pull of full-blown recovery operations, you’ll need a full-blown set of backups that provide complete coverage of your database since your last FULL backup. That, and it should go without saying that being able to execute a full-blown recovery operation is something that you should be already familiar with – as this operation has obvious scope and applicability outside of problems dealing with mere corruption.

However, just note that in cases where you are using a FULL recovery operation to recover from corruption, you’ll be using your log-file backups to play PAST the time where corruption occurred (unlike what you MIGHT be doing as part of a point-in-time recovery where you’re trying to restore to a point in time BEFORE an end user, for example, deleted an entire table). Otherwise, FULL recovery operations are pretty straight forward – and is going to be MUCH easier if you use SQL Server Management Studio’s GUI or the GUI provided by your third party backup solution – as the approach outlined in Appendix 1 that involves scripting is really only useful for page-level restoration.

Accordingly, the order of operations is like so:

ALTER DATABASE  SET SINGLE_USERWITH ROLLBACK AFTER 10 SECONDSGO

Where the number of seconds to wait before forcing a rollback is something you might want to modify.
BUT, the key thing here, obviously, is that in executing a full-blown recovery, you’re OBVIOUSLY taking your database offline.

And, again, note that executing a FULL recovery operation is something that you should be VERY familiar with. As such, the best practices recommendation is that you execute tests on a regular basis – either by restoring copies of backup on another server (where you don’t have to worry about collisions with existing databases) or where you execute these recovery operation on your existing server – but use the GUI to re-map file locations AND make sure to restore databases to a DIFFERENT database – instead of overwriting over the top of your production database. For more information and examples, see: Restoring Databases with SQL Server Management Studio.

Up Next

In the next installment of this series on corruption I’ll provide a set of step-by-step instructions that you can use as a way to help practice dealing with recovery operations with corrupt databases – in the sense that I’ll provide a ‘demo’ or ‘script’ you can follow-along with in order to corrupt a database, see the problems it causes, and then work through recovery operations on your own to get a good feel for what corruption looks like and how to correct it.

Part XII: Recovery Sample