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

By:   |   Updated: 2008-12-12   |   Comments (4)   |   Related: 1 | 2 | 3 | 4 | > Disaster Recovery


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?

Solution

To continue the series (Disaster Recovery Procedures in SQL Server 2005 Part 1, Disaster Recovery Procedures in SQL Server 2005 Part 2 (Isolating Critical Objects), Disaster Recovery Procedures in SQL Server 2005 Part 3 (Using Partitioned Tables with Multiple Filegroups for High Availability)) on disaster recovery scenarios in SQL Server 2005, let us look at another means to increase the availability of your highly critical databases. There are cases where your very large databases may get corrupted. This may be caused by faulty disk driver or controller or sometimes by a incorrectly configured anti-virus software running on the SQL Server machine. You definitely need to have a full database backup to get the corrupted data back. But you wouldn't want to restore the entire database if only a few pages are corrupted. SQL Server 2005 introduced the concept of page-level restore. This gives you the option to restore one or more damaged pages without restoring the entire database. In the enterprise edition, page restores are performed online when conditions allow, which means higher availability for your databases.

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).

2

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

1

Let's run the DBCC CHECKDB 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.

3

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.

USE master 
GO 
RESTORE DATABASE Northwind  
PAGE = '1:265' 
   FROM DISK = N'D:\DBBackup\NorthwindBackup.bak' 
GO
4

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.

BACKUP LOG Northwind   
   TO DISK = N'D:\DBBackup\Northwind_log.TRN'  
   WITH INIT, NO_TRUNCATE,  
   STATS = 10  
GO

Finally, we restore the tail of the log which we backed up as part of the recovery process to get the database back online.

RESTORE LOG Northwind    
   FROM DISK = N'D:\DBBackup\Northwind_log.TRN'
   WITH RECOVERY,   
   STATS = 10   
GO

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-12-12

Comments For This Article




Wednesday, April 30, 2014 - 11:04:14 AM - bass_player Back To Top (30565)

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

Tuesday, April 29, 2014 - 7:04:56 PM - eric Back To Top (30553)

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?

Monday, March 23, 2009 - 4:53:12 PM - bass_player Back To Top (3063)

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

select name, page_verify_option_desc
from sys.databases
order by name

Sunday, January 4, 2009 - 5:32:53 PM - add2700 Back To Top (2497)

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















get free sql tips
agree to terms