Need help with Online Page-Level Restores? SQL11 SSMS is your friend!

SQL11 Denali has a new Page-Level Restore Dialog in SSMS

In SQL Server 2005, Microsoft introduced the Online Restore where parts of a database could be restored while other parts of the same database could remain available. In the same release, the Page-Level Restore was also introduced. The combination of the two technologies is the Online Page-Level Restore which allows one or more data pages to be restored while the database remains available. This is a great feature for 24x7 shops but takes more than a little skill and practice. SQL11 Denali makes it easy. First of all, the Online Page-Level Restore is a feature of the Enterprise Edition and above. It also requires the Database to be in Full or Bulk-logged Recovery Model, with Full Recovery Model preferred. Its main goal is to be able to restore and repair damaged pages without taking the rest of the database offline. Certain special data pages cannot be restored online but may be restored offline. An Offline Restore is the classic way of doing things where the whole database is taken offline. This type of restore can be done with any edition. A Data Page can be damaged in a variety of ways. For instance, during a power failure or hardware issue, a data page can become corrupt and marked “suspect”. The PAGEVERIFY setting on the database defines how the database picks up on suspect pages. The recommended setting and default is now CHECKSUM which involves a mathematical calculation for each page. If things don’t add up, the page is marked suspect. The usual symptom is an 823 or 824 error in the Error Log. If you are lucky, only a few rows are affected. If you are unlucky, the page may affect a whole table (if it is the root page) or the whole database (if it is the database boot page). SQL Server makes an entry in the MSDB table “dbo.suspect_pages” for each such data page. An event_type of 2 is a CHECKSUM error. The Online Page-Level Restore has a unique restore sequence. Normally we follow the strategy of backing up the “tail” of the log first, when we know that a database needs to be restored. This captures the latest updates for recovery even if the data files are damaged or unavailable. However, with an Online Page-Level Restore, we are told to Restore the damaged page(s) first using the PAGE clause of the RESTORE DATABASE statement, then restore the chain of log backups since that backup was made. Only then should we backup the “tail” of the log and then immediately restore it again. This has the effect of bringing the restored page up to date and synced with all the other pages. This special sequence is all documented in Books Online. It’s a tricky sequence and not intuitive. But it works. So how does SQL 11 Denali SSMS help out? Right-click the database in Object Explorer, then under Tasks, select Restore then choose Page…The Restore Page dialog appears. If you don’t know the page numbers that are suspect, click “Check Database Pages” – this does a DBCC CHECKDB PHYSICAL_ONLY and will copy the suspect page numbers into the list. Alternatively, you can look up the damaged page number(s) in the Error Log messages or in the suspect_pages table and enter them manually. An example page number may be of the format “1:743” which translates to a File Id of 1 and a Page Id of 743. SSMS requires you to enter the File Id and Page Id separately. SSMS knows which backups have been made via the Backup History tables in MSDB so will suggest a valid restore sequence. You can click the Verify button to check the validity of the backups. You can also click the Script/New Query Editor Window option to generate a valid script before clicking OK or Cancel (if you prefer to examine the script before running). In my testing with CTP3, the script was valid and the dialog worked as designed using the upgraded version of AdventureWorks2008R2 database (available on codeplex.com) as a sample. The Online Page-Level Restore is a powerful feature that is normally a complex operation to pull off but now SQL 11 Denali SSMS makes it easy. Watch this space for more cool Denali features. Cheers Brian MSDN SQL Server Denali Page Restores: http://msdn.microsoft.com/en-us/library/ms175168(v=SQL.110).aspx

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2011 IDG Communications, Inc.

IT Salary Survey: The results are in