Page level restore is not something new for us, however page restore via SSMS (GUI) is something new, and it’s a feature available for SQL Server 2012.

Page level restores is a great flexible option to restore a particular page from the backup in the event of a corruption been identified for one or more pages.

Would you really like to restore a 50GB backup set when there is a requirement to replace a single corrupted page? No, you won’t and this is where page restore comes in.

You can find your corrupted pages and replace it with a restore.

Till SQL 2012 page level restore was possible via T-SQL code, and SQL 2012 provides you this option directly from the UI.

Let’s do a quick demo to understand this feature better.

Demo

1. For the purpose of this demo I will be using a database called Test_Page_Restore which is an exact replica of Adventureworks2012 database (Available in codeplex)

2. I am interested in corrupting an index page for Table HumanResources.Employee.

Let’s find an Index page for this table.

DBCC ind('Test_Page_Restore', 'HumanResources.Employee', 1)

Wait!!! Before I do some serious corruptions, let me take a quick backup of the database to the disk.

I will be taking a Full Backup directly.

BACKUP DATABASE [Test_Page_Restore] TO DISK= 
'C:\Test_Backups\Test_Page_Restore_BEFCorruption.bak' WITH stats=10

3. As I have a backup now,I can afford to do some real interesting corruptions !

Note – A backup set doesn’t mean that you are good and you have a sound disaster recovery plan. A backup is valid if and only if it can be restored. As I am doing a demo in a controlled environment I am not worried about backup validity.

I picked page ID 985 and will be corrupting it. I will also need to find the offset of the page for corrupting it and in my case it will be 985*8192(No of bytes for the page)  = 8069120

I am using hex-editor called XVI32 to corrupt the pages, and I have learned this technique from Kendra Little(B/T)

Note –  You should never ever try these steps in a production environment. It should be done only in a controlled LAB environment.

Before opening the data file using hex-editor I will need to take the database offline.

ALTER DATABASE [Test_Page_Restore] SET offline

Once the database is offline I can open the data file using the hex-editor. Using the above mentioned offset, I can go to the exact page and corrupt it.

I went to the exact page and corrupted that page by entering some letters. Once done, I saved the file directly from hex-editor and closed it.

4. Technically speaking now I have wonderfully corrupted page ID 985.

I can proceed and take the database Online Now.

ALTER DATABASE [Test_Page_Restore]  SET online

5. I am pretty sure that if I do a Select * from the table, I will get I/O errors.

Let’s test that out now.

SELECT TOP 1000 * 
FROM   [Test_Page_Restore].[HumanResources].[employee]

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: 
incorrect checksum (expected: 0x77d4e0e1; actual: 0x5cd4e0e1). 
It occurred during a read of page (1:985) in database ID 5 at offset 0x000000007b2000 
in file 'C:\Program Files\Microsoft SQL Server\
MSSQL11.SQL2012A\MSSQL\DATA\Test_Page_Restore.mdf'. 

The error message clearly says that I have a corrupted page and it matched the one which I corrupted using hex-editor.

Yay!!!

6. As mentioned earlier, starting SQL 2012 we can do a corrupt page restore directly from SSMS.For this I will need to right click the database and choose Restore option and then Page.

7. As soon as I select this option I will get the new restore window.

If you carefully look at the Pages section, it already did a DBCC CHECKDB and pulled the corrupted page from the database.

That’s one real cool option to have.

It exactly matched the page which I corrupted earlier.

The wizard will also let you know from which backup set you can restore the corrupted page back. In my case, I had already taken a backup earlier and it picked it up automatically.

8. I did a restore of the page using the wizard to bring back the corrupted page.

9. As the restore is completed. I checked the data to see if everything is fine.

SELECT TOP 1000 * 
FROM   [Test_Page_Restore].[HumanResources].[employee] 

Great we are back in action yet again.

I would like to thank Paul Randal(B/T) and Kendra Little(B/T) here as I learned to work with Pages/Corruption/Repairs by attending their sessions and reading their articles. They are just awesome.

 Conclusion

SQL Server 2012 has multiple enhancements and GUI support for Page restores are just one of them.

Thanks for reading.