If you are a SQL Server DBA then in the previous versions there were interesting scenario’s you never wanted to get into. A corrupt DB state is always a night mare and there weren’t well documented way of recovery from this state. One needs to understand some of the nuances of what has gone wrong and then based on this the recovery steps would vary. From metadata corruption, index corruption, page corruption etc the variations are many.
I am a big time supporter of enabling CHECKSUM on databases because these corruptions of 823, 824 can be easily caught at query time. And doing a page level restore was a delicate process yet there were points where mistakes can happen. Especially, if you forget to restore the tail :).
Setting up the environment
For the demo I have gone ahead and done the following. You are free to do the same.
- Create database (CorruptDB) and populate a table.
- Take Full Backup.
- Take it offline.
- Corrupt a Page
- Brought it online
Once this was done. We just access the table, an 824 error is got as below.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error:
incorrect checksum (expected: 0x7ddd3463; actual: 0x6dcd3473).
It occurred during a read of page (1:274) in database ID 7 at offset
0×00000000224000 in file ‘C:\Temp\CorruptDB.mdf’.
Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately.
Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Something’s that I like about the error message is – it surely guides you to the next steps. Perform a DBCC CHECKDB to see the level of damage. I prefer to use the following command as it suppresses informational messages.
From this we can see it is a single page (1:274) that has got damaged and the CHECKDB also suggests another option of repair_allow_data_loss. I highly caution you NOT to use this option as your first option but as a last resort. Understand using that option can make your database values in an inconsistent state. Please read the BOL for more details.
Page Restore – SQL 2012 way
In SQL Server Management Studio 2012, you will now have access to a Page Restore Dialog to allow easy database page restores through a simple user interface.
The goal of a page restore is to restore one or more damaged pages without restoring the whole database. Page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
Right click on the database in question, go to Tasks, then select Restore and you will see the Page… option.
Once the Restore Page dialog is open you can check for page corruption (click Check Databases Page button) and have dialog create the restore plan for you to be run later (script options) or run immediately.
Check Database Pages: This will execute DBCC CHECKDB … WITH PHYSICAL_ONLY on the selected database. This is a long running operation based on your DB size and its progress can be tracked and cancelled by the Progress Monitor on the dialog’s framework.
This grid of FileID and PageID is populated from the msdb.suspect_pages and the user can manually add and remove pages.
DONOT do this step now: If you click OK now, the page restore starts and you will see the following confirmation with 100% green on the status in the top left.
At this point, I highly recommend you to script using the Script dropdown from top to understand the exact steps. A typical steps will be:
- It restores the corrupt page. (WITH NORECOVERY MODE)
- It backs up the tail of the log.
- It restores all necessary log backups
- Finally, it restores the tail of the log.
Personally, if you don’t have SQL 2012 then these are the steps anyways. You can see in the UI the Tail-Log Restore mention. In the above UI, I don’t have any Log backup’s taken since the full backup. This UI in SQL Server 2012 really impresses me.
There are surely restrictions and special cases for using Page level restore and we highly recommend taking some time reading it. One thing is sure, with SQL Server 2012 Management Studio the process for Page restore has surely been simplified and most importantly, the basics still remain the same.
If you don’t understand the nuances of Page restores, please don’t try to mess around with the DB’s or this dialog on production environments. The basis of Page restore is on the fact that there is a backup to start with. So please make sure your backup strategies are in place and you are on Full recovery model.
This entry was posted on Thursday, August 30th, 2012 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.