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 <yourDbNameHere> SET SINGLE_USER WITH ROLLBACK AFTER 10 SECONDS GO
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.
- Set the database to SINGLE_USER_MODE – using syntax similar to that listed below:
-
Back up the tail end of the log. Again, this is CRITICAL to ensuring that data is not LOST – because you’ll be using the transaction log’s play-by-play record of all changes to the database to ‘play through’ an operation that completed and was written to disk – only to become ‘lost or corrupt’ when written to disk. And the hope, of course, is that when you replay this transaction or operation the second time (as part of recovery), it won’t be corrupted/lost when written to disk as whatever ‘corrupted’ the data previously was some sort of bug/problem/fluke at the disk subsystem level that should effectively NEVER repeat itself.
NOTE: Depending upon security settings/configuration and whether or not you’re using a third party backup solution (or not) you might need to ‘pop’ your database out of SINGLE_USER mode in order to execute a log file backup. If you need to do this, try setting it to RESTRICTED_USER instead as that only allows dbo members access rather than what would, hopefully, be the ‘unwashed masses’ who might otherwise have access to the system while t-log backups are being executed. - Restore the database using your GUI. For an example of how to do this using native SQL Server backups, see the following video: Restoring Databases with SQL Server Management Studio. Or, use the GUI provided by your third party backup solution. In either case, this operation is something you should be VERY comfortable with – by means of regular practice.
- If you had corrupt pages, make sure to clear msdb..suspect_pages by executing a DELETE command – just so that your NEXT problem isn’t combined with this one (now that this issue or problem has been cleared up).
- Once recovery is complete, release the database back into production by setting it to MULTI_USER as outlined above.
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
