Restore Damaged Pages from DB Backup

Are your database pages corrupt or damaged? Now you can restore damaged pages without impacting the availability of the database. SQL Server provides an extension to the RESTORE command that can help restore damaged pages from the backups. (Note: Only the portion that is being restored will be unavailable)

Important things to Keep in Mind:

1. You cannot restore log pages
2. You cannot restore GAM, SGAM and PFS pages.
3. You cannot restore Page 0 of all data files (Page 0 is the file boot page)
4. You cannot restore Page 1:9 (Page 1:9 is a database boot page)
5. You cannot restore any Full-Text catalog pages
6. The database you are performing portions of restore on should be using either FULL or BULK-LOGGED recovery model.
7. You cannot restore pages on read-only file groups
8. The restore sequence MUST be FULL and then FILE or Filegroup backup.
9. The page restore requires an unbroken chain of log backups up to the current log file, and must be applied to bring the page up to date with the current log file.
10. Page restores must be incremental.
11. You cant perform both database backup and page restores concurrently.
12. If you had performed backups using multiple devices then you would need the same number of devices during the online PAGE or FILE restore.

Let’s now take a look at an example of restoring certain pages from the backup. For this, you need the file ID and page ID: (PAGE = FileID:PageID). Lets assume you want to restore page numbers 35, 67, 87, 103 with fileID=1 for SQLCOMMUNITY database.

RESTORE DATABASE [SQLCOMMUNITY] PAGE='1:35, 1:67, 1:69, 1:103'
   From SQLCommunity_BackupFile
   With NORECOVERY
Go

Here’s the step-by-step instructions on performing page level restore from a backup:

Step 1: First identify the damaged page id from Error logs, Event traces, DBCC CheckDB command, WMI Provide, etc. (A CHECKSUM or torn write error returns page ID that can be used for performing PAGE level restore)

Step 2: Start a PAGE level restore with a FULL or FILEGROUP backup that contains the page. (You can use the PAGE clause in the RESTORE DATABASE command and specify all the PAGE IDs of all the pages to be restored. (You can only restore 1000 pages per one RESTORE command)

Step 3: Apply any available differentials required for the pages being restored.

Step 4: Apply any T-Log backups if any.

Step 5: Create a new T-Log backup of the database that includes the last LSN (Log Sequence Number) of the restored pages, that is, the point at which the last restored page is taken offline. The final LSN, which is set as part of the first restore in the sequence, is the redo target LSN. Online roll forward of the file containing the page is able to stop at the redo target LSN.

Step 6: Restore the new log backup that was taken in Step 5. Once the new new T-log backup is restored, the page restore is complete and the pages are now usable.