Page Restore Dialog in SQL Server 2012 August 30th, 2012

Vinod Kumar

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.

  1. Create database (CorruptDB) and populate a table.
  2. Take Full Backup.
  3. Take it offline.
  4. Corrupt a Page
  5. 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
0x00000000224000 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.

image

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.

image

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.

image

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:

  1. It restores the corrupt page. (WITH NORECOVERY MODE)
  2. It backs up the tail of the log.
  3. It restores all necessary log backups
  4. 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.

Final thoughts

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.

Credits: Thanks to Balmukund Lakhani (Technical Lead with Customer Support Team)  for helping me with Page Corruption. And he is also a blogger at: http://sqlserver-help.com/

Tags: , , , , , , ,

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.


10 Responses to “Page Restore Dialog in SQL Server 2012”

  1. […] current process done by my networking team”. Interestingly, a lot of people fail to understand how Page level restore needs to be […]

  2. Rohit says:

    Nice blog Vinod.
    Just one query…. How should I corrupt a database page to reproduce this.

    Thanks,
    Rohit

    • Vinod Kumar says:

      :) Rohit – The blog is about how you can recover from a corrupt page and not how we can corrupt an existing database. There are multiple blogs on the internet that show you the methods. The simplest method is to use some Hex Editor and change a known string. But use it with caution and without knowing the nuances, the DB can become completely unusable in no time.

      • Rohit says:

        Yeah… but was just inquisitive as how can I do this :) will search this, test and will come back if I find anything.
        Thx again!!!

  3. Shashikant Shakya says:

    Page level restore through SSMS Geart!! :)

    Waiting for this since u have posted on FB.

    Thanks.

  4. This is very, very cool indeed. Today, we have huge scripts that do page-level restores. Thank-you for writing about it in detail!

    Have a nice day!

  5. Anil says:

    Great article. Just in time for me
    To explore the good ol days of dealing with suspect/ corrupt databases.

    Cheers

    Anil

    • Vinod Kumar says:

      Anil – this post was to just show the Page Restore dialog. It surely misses the cases where the backup is missing or virtually non-existent. Thanks.

Leave a Reply