Saturday, March 17, 2012

Step-by-step instructions on how to to perform Online Page Restore in SQL Server 2005

It was always painful for me to take backups in SQL Server and then performing a safe restoration of those backups. Perhaps, restoration is the most important job that should be performed cautiously in order to save time and effort. In a demanding business environment, a user may be required to create multiple backup copies and restore critical database pages. In such circumstances, the need for a standard restoration technique comes into picture.

I was using SQL Server 2005 version and 'Online Page Restore' is one of the functionalities included with its Enterprise edition. This feature is immensely helpful when only a few pages are damaged in the database. However, it has a downside as this feature doesn't work for transaction logs, file header page (PageId =0 ), allocation pages (GAM, SGAM, PFS, DIFF, ML), full-text and boot page. Moreover, the restoration procedure should start with the first backup as a full, file or filegroup backup and all the log backups should form an uninterrupted chain up to the most recent log. This helps to keep the database page undated and fully consistent.

It was important to first examine the database and determine all corrupt pages. In any event of database corruption, the two most prominent errors that show up frequently are 'Error 823' and 'Error 824'. As I took snapshots at each occurrence of these errors, I already gathered sufficient information. They provided the database id and the offset or number of the damaged page. Also, I checked the SQL Server Error Log and ran the DBCC CHECKDB command. The 'MSDB.dbo.suspect_pages' table has a row corresponding to each page that encountered error 823 or 824 during its read operation. This helped me to ensure that I didn't miss out on a page.

I had the full database backup and also, other differential and log backups up to now. I started with the restore operation using the 'RSTORE DATABASE' command with page clause as follows:

For restoring database-

FROM DISK = 'D:\Backup\CorruptDB_full.bak'

For log-

FROM DISK = 'D:\Backup\CorruptDB_log1.bak'

Here, I used 'norecovery' option to restore a full backup with pageid '80'. This was successful and I got back all the data intact. At the same time, I learned about commercial SQL database recovery tools. These utilities prove to be worthwhile in such cases of corruption and help to easily recover all inaccessible or damaged SQL database objects.

No comments:

Post a Comment