About performing a SQL Server page-level restore
Note:
Page-level restores are only applicable for SQL Server legacy backup policies.
If a portion of a SQL Server database is corrupted due to hardware failure, you may be able to use page-level restore. Use page-level restore to recover only the pages that were corrupted. Page-level restore can reduce the total downtime if you only need to restore a relatively small number of pages. If many pages are corrupt, then a full database recovery may be faster.
When you select the page restore option, NetBackup for SQL Server creates a page restore template.
This template includes the following parts:
A page restore operation that you can modify by inserting the IDs of the pages that you want to restore.
A series of transaction log images for recovering the database to the current point in time.
A tail-log backup and recovery operation, which is required to bring the database online.
The following requirements and limitations exist when you perform SQL Server page-level restores:
Pages can be restored from the following backup types: Database, filegroup, file, read-write filegroups, and partial database.
Your SQL Server must use either the full or bulk-logged recovery model.
SQL Server sometimes cannot recover the specific pages that you request if they contain critical information about the definition of the database itself. For example, you cannot use page-level restore for the first page in a database file. When you detect that page-level restore does not work, you need to use full database recovery.
A maximum of 1000 pages can be recovered from a backup image through a page-level restore.
This topic describes how to perform page-level restores. Note that the Microsoft SQL Server service must have full access permission to the folder install_path\netbackup\dbext\mssql\temp.
To perform a page-level restore
- Obtain a list of corrupt pages in the database.
SQL Server Books Online suggests several methods for obtaining a list of corrupt pages. One of these methods is to run the command DBCC checkdb from the SQL Server Management Studio.
- Browse for the backup images you want to restore.
- In the Restore Microsoft SQL Server Objects dialog box, expand the database instance.
- Expand the database.
- Select the database backup image that contains pages you want to restore.
- From the Scripting list, select Create a page restore template.
- Click Restore.
- In the Save Script As dialog box, type a file name for the page restore script and click Save.
- Click Yes to open the template in Notepad.
- Edit the page first operation the page IDs that you want to replace.
For example, replace:
# # Create one or more page restore requests. These use the following format #PAGE file-id:page-id
with
# # Create one or more page restore requests. These use the following format PAGE 1:14 PAGE 1:20
- When you finish modifying the template, save it.
- To run the restore, select File > Manage script files, select the script you created, and click Start.
- Click Yes to start the restore.
More Information