Using Page Level Restore as a Disaster Recovery Procedure in SQL Server 2005

Problem

In a previous tip on Disaster Recovery Procedures in SQL Server 2005 Part 1, we have seen how we can come up with a disaster recovery procedure in SQL Server 2005. There are other ways to increase availability of your highly critical database in SQL Server 2005. What are those other options?

Let's have a look at how to use page-level restores on a corrupted database. I will still be using the Northwind database for this tip - except that the database version that I will be using is corrupted. Note that it is not that easy to generate a corrupt database in your production environment so you would have to create one on your own to test these procedures. What I did was to use a hex editor to modify the values of the database file. This would introduce inconsistencies in the database file causing it to be corrupt. You would need to do some trial-and-error to get a specific page corrupted. In my case, I have chosen to corrupt a data page affecting the Orderstable as it would be easy to find the text values of the records inside this table (as compared to the Order Details table that I have been using in the previous examples which contains mostly numerical data) from the hex editor.

Running a query on the corrupted Orders table would return partial results even though a corrupted page was detected on the Northwind database. Looking at the Resultstab, it looks like that the damaged record pertain to row number 673 (the original Orders table contains 830 records).

In my case, from the error message, page 265 of file 1 is inaccessible.

Let's run the DBCCCHECKDB command to check the integrity of all the objects in the Northwind database. The result simply confirms that page 265 - the Orderstable - of the database cannot be processed.

Now, if this was a very large database, you wouldn't just restore the full database backup. That would render your database inaccessible during the time that it is being restored and that's the last thing you want to do, especially if only a few pages are damaged. Notice that even though there are corrupt pages in the database, all the other objects are still accessible and we want to keep it that way. For this scenario, we can just use the page-level restore option in SQL Server 2005. To do that, we specify the file and page numbers that we got from the DBCC CHECK command in our RESTORE DATABASEcommand.

If your database contains more than one damaged page, simply add the file and page numbers accordingly in the PAGE parameter and separated by commas.. You can use either a full, file or filegroup backup to do the page restore provided that the backups that you use are valid and contain copies of the damaged pages. I simply used a full database backup in this example to demonstrate how to use the page-level restore option. This restore process simply replaces the pages specified in the RESTORE DATABASE command. Notice that the message returned by the RESTORE DATABASE command gives us a hint on what to do next. As always, we backup the tail of the log to complete the roll forward process.

You can validate if the restore process is successful by running a query on the Orders table. The page-level restore option enables us to restore a damaged portion of the database as quickly as possible without having to restore the entire database. Your restore sequence will depend on the types of backups you are doing as this is a more granular approach in recovering a database. Let's say you have regular transaction log backups running every hour, you will need to restore those log backups in sequence before backing up the tail of the log as you need to keep the unbroken chain of log backups up to the current log file so that the page is brought up to date with the current log file.

Next Steps

You can download the corrupt Northwind database files and backups used in the sample here. The database backup is so that you have a consistent database state before you proceed with testing the procedures outlined above.

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter
I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.

This really depends on how you rconfigure TSM. If you are using the TSM Agent for SQL Server, you need to use the TSM commands to perform a point-in-time restore. If you are only using TSM to backup the file system files, you need to restore the files first on the local file system and use the native SQL Server commands to restore the transaction log backup. Either way, you need to test which one to use and document how to perform the disaster recovery process

I'm currently backing up my databases to Tivoli Storage Manager Backup System so I would suspect I'd have to first backup my log locally .trn file then restore from TSM no recovery option then I would I be able to choose to restore from my local .trn backup?

By default, all databases in SQL Server 2005 has the PAGE_VERIFY option set to CHECKSUM so this should be ok. If you have upgraded your databases from SQL Server 2000, the settings will be retained - either TORN_PAGE_DETECTION or NONE, depending on how it was configured prior to upgrade. A recommendation is to set the PAGE_VERIFY option in SQL Server 2005 to CHECKSUM. Just validate that your databases are set to CHECKSUM

I was under the impression that you had to first set PAGE_VERIFY option.

http://msdn.microsoft.com/en-us/library/bb402873(SQL.90).aspx

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.