Performing Page Restores
This topic is relevant for SQL Server databases that are using the full or bulk-logged recovery models. Page restore is supported only for read/write filegroups.
The goal of a page restore is to restore one or more damaged pages without restoring the whole database. Typically, pages that are candidates for restore have been marked as "suspect" because of an error that is encountered when accessing the page. Suspect pages are identified in the suspect_pages table in the msdb database.
Note
Not all page errors require a restore. A problem can occur in cached data, such as a secondary index, that can be resolved by recalculating the data. For example, if the database administrator drops a secondary index and rebuilds it, the corrupted data, although fixed, is not indicated as such in the suspect_pages table.
Multiple database pages can be restored immediately. The log file backups are applied to all database files that contain a page that is being recovered. As in a file restore, the roll forward set is advanced with a single log redo pass.
Page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation. However, if you have to restore more than a few pages in a file, it is generally more efficient to restore the whole file. For example, if lots of pages on a device indicate a pending device failure, consider restoring the file, possibly to another location, and repairing the device.
Page Restore Scenarios
All editions of SQL Server 2005 and later versions support restoring pages when the database is offline (offline page restore). In SQL Server 2005 Enterprise Edition and later versions, if the database is online during a page restore, the database remains online. Restoring and recovering a page while the database is online is called an online page restore.
These page restore scenarios are as follows:
Offline page restore
SQL Server 2005 Standard, SQL Server 2005 Express Edition, and SQL Server 2005 Workgroup, and later versions, support only offline restore. SQL Server 2005 Enterprise Edition and later versions use offline restore if the database is already offline. In an offline page restore, the database is offline while damaged pages are restored. At the end of the restore sequence, the database comes online.
For the page restore to succeed, the restored pages must be recovered to a state consistent with the database. An unbroken chain of log backups must be applied to the last full or differential restore to bring the filegroup that contains the page forward to the current log file.
Online page restore
In SQL Server 2005 Enterprise Edition and later versions, when conditions allow, page restores are automatically performed online. In most cases, a damaged page can be restored while the database, including the filegroup to which a page is being restored, remains online. Online page restore is especially useful for pages damaged by hardware error.
Occasionally, a damaged page can require an offline restore. For example, damage to certain critical pages might prevent the database from starting. In these cases, offline restore must be used.
Note
An online restore tries to update metadata, and that update might fail if a critical page is involved. If an online restore attempt fails, the restore must be performed offline.
Page restore takes advantage of the improved page-level error reporting (including page checksums) and tracking in SQL Server 2005 and later versions. Pages that are detected as corrupted by check-summing or a torn write, damaged pages, can be restored by specifying the pages in a RESTORE statement. Page restore is intended for restoring only a few damaged pages. Each page specified in a RESTORE statement is replaced by a page from the specified backup set. The restored pages must be recovered to a state consistent with the database. Only explicitly specified pages are restored.
Limitations of Page Restores
Only database pages can be restored. Page restore cannot be used to restore the following:
Transaction log
Allocation pages: Global Allocation Map (GAM) pages, Shared Global Allocation Map (SGAM) pages, and Page Free Space (PFS) pages. For more information, see Managing Extent Allocations and Free Space.
Page 0 of all data files (the file boot page)
Page 1:9 (the database boot page)
Full-text catalog
If an individual page cannot be restored, you must use the existing full database backup or full file or filegroup backup.
Note
If the page being restored is a special case, such as metadata pages, the online page restore will fail. In these cases, try an offline page restore.
Requirements for Restoring Pages
A page restore is subject to the following requirements:
The databases must be using the full or bulk-logged recovery model. Some issues exist if you are using the bulk-logged model. For more information, see the following section.
Pages in read-only filegroups cannot be restored. Trying to make a filegroup read-only will fail if there is a page restore going on at the same time in the filegroup.
The restore sequence must start with a full, file, or filegroup backup.
A page restore requires an unbroken chain of log backups up to the current log file, and they must all be applied so that the page is brought up to date with the current log file.
As in a file-restore sequence, in each restore step, you can add more pages to the roll forward set.
A database backup and page restore cannot be run at the same time.
Bulk-logged Recovery Model and Page Restore
For a database that uses the bulk-logged recovery model, page restore has the following additional conditions:
Backing up while filegroup or page data is offline is problematic for bulk-logged data, because the offline data is not recorded in the log. Any offline page can prevent backing up the log. In this cases, consider using DBCC REPAIR, because this might cause less data loss than restoring to the most recent backup.
If a log backup of a bulk-logged database encounters a bad page, it fails unless WITH CONTINUE_AFTER_ERROR is specified.
Page restore generally does not work with bulk-logged recovery.
A best practice for performing page restore is to set the database to the full recovery model, and try a log backup. If the log backup works, you can continue with the page restore. If the log backup fails, you either have to lose work since the previous log backup or you have to try running DBCC must be run with the REPAIR_ALLOW_DATA_LOSS option.
Basic Page Restore Syntax
To specify a page in a RESTORE DATABASE statement, you need the file ID of the file containing the page and the page ID of the page. The required syntax is as follows:
RESTORE DATABASE database_name
PAGE ='file:page [ ,...n ]' [ ,...n ]
FROM <backup_device> [ ,...n ]
WITH NORECOVERY
For more information about the parameters of the PAGE option, see RESTORE Arguments (Transact-SQL). For more information about the RESTORE DATABASE syntax, see RESTORE (Transact-SQL).
Procedure for a Page Restore
The basic steps for a page restore are as follows:
Obtain the page IDs of the damaged pages to be restored. A checksum or torn write error returns page ID, providing the information required for specifying the pages. To look up page ID of a damaged page, use any of the following sources.
Source of page ID
Topic
msdb..suspect_pages
Error log
Event traces
DBCC
WMI provider
Start a page restore with a full database, file, or filegroup backup that contains the page. In the RESTORE DATABASE statement, use the PAGE clause to list the page IDs of all of the pages to be restored.
PAGE ='file:page [ ,...n ]'
Apply the most recent differentials .
Apply the subsequent log backups.
Create a new log backup of the database that includes the final LSN 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. To learn the current redo target LSN of a file, see the redo_target_lsn column of sys.master_files. For more information, see sys.master_files (Transact-SQL).
Restore the new log backup. After this new log backup is applied, the page restore is completed and the pages are now usable.
Note
This sequence is analogous to a file restore sequence. In fact, page restore and file restores can both be performed as part of the same sequence.
Example
The following example restores four damaged pages of file B with NORECOVERY. Next, two log backups are applied with NORECOVERY, followed with the tail-log backup, which is restored with RECOVERY.
Important
If damaged pages are storing critical database metadata, an offline page restore sequence might be necessary. To perform an offline restore, you must back up the transaction log WITH NORECOVERY.
The following example performs an online restore. In the example, the file ID of file B is 1, and the page IDs of the damaged pages are 57, 202, 916, and 1016.
RESTORE DATABASE <database> PAGE='1:57, 1:202, 1:916, 1:1016'
FROM <file_backup_of_file_B>
WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
WITH NORECOVERY;
RESTORE LOG <database> FROM <log_backup>
WITH NORECOVERY;
BACKUP LOG <database> TO <new_log_backup>
RESTORE LOG <database> FROM <new_log_backup> WITH RECOVERY;
GO