Detecting and Coping with Media Errors During Backup and Restore
Microsoft SQL Server 2005 and later versions give you the option of recovering a database despite detected errors. An important new error-detection mechanism is the optional creation of a backup checksum that can be created by a backup operation and validated by a restore operation. You can control whether an operation checks for errors and whether the operation stops or continues on encountering an error. If a backup contains a backup checksum, RESTORE and RESTORE VERIFYONLY statements can check for errors.
Note
Mirrored backups provide up to four copies (mirrors) of a media set, providing alternative copies for recovering from errors caused by damaged media. For more information, see Using Mirrored Backup Media Sets.
Checksums during BACKUP
SQL Server supports three types of checksums: a checksum on pages, a checksum in log blocks, and a backup checksum. When generating a backup checksum, BACKUP verifies that the data read from the database is consistent with any checksum or torn-page indication that is present in the database.
The BACKUP statement optionally computes a backup checksum on the backup stream; if page-checksum or torn-page information is present on a given page, when backing up the page, BACKUP also verifies the checksum and torn-page status and the page ID, of the page. When creating a backup checksum, a backup operation does not add any checksums to pages. Pages are backed up as they exist in the database, and the pages are unmodified by backup. Due to the overhead verifying and generating backup checksums, using backup checksums poses a potential performance impact. Both the workload and the backup throughput may be affected. Therefore, using backup checksums is optional. When deciding to generate checksums during a backup, carefully monitor the CPU overhead incurred as well as the impact on any concurrent workload on the system.
Note
BACKUP never modifies the source page on disk nor the contents of a page.
The following BACKUP options control the backup checksum behavior:
CHECKSUM
Requests that the backup operation verify each page for checksum and torn page, if enabled and available, and also to generate a checksum for the entire backup.
If checksum verification is requested during a backup operation:
Prior to writing a page to the backup media, BACKUP verifies the page-level information (page checksum or torn page detection), if either exists. If neither is present, backup cannot verify the page; the page is included as is, and the contents are added to the overall backup checksum.
Note
For more information about page checksums and torn page detection, see the PAGE_VERIFY option of the ALTER DATABASE statement. For more information, see ALTER DATABASE (Transact-SQL).
For optional use at restore time, backup generates a separate backup checksum (a backup checksum).and records this on the backup media, regardless of whether page checksums are present.
The backup set is flagged as containing backup checksums (in the has_backup_checksums column of msdb..backupset). For more information, see backupset (Transact-SQL).
Note
For log backups, the backup checksums are generated and verified.
NO_CHECKSUM
Explicitly disables page validation and the generation of backup checksums. (This is the default behavior.)
Controlling the Response to an Error
When CHECKSUMS is specified, if BACKUP encounters a page error during verification, the backup fails. The following BACKUP options control this behavior:
CONTINUE_AFTER_ERROR
Instructs BACKUP to continue despite encountering an invalid backup checksum. In this case, BACKUP:
Flags the backup set on the backup media as containing errors and tracks the page in the suspect_pages table in the msdb database. For more information, see suspect_pages (Transact-SQL).
Logs the error in the SQL Server error log.
Marks the backup set as containing this type of error (in the is_damaged column of msdb.backupset). For more information, see backupset (Transact-SQL).
Issues a message that the backup was successfully generated, but contains page errors.
STOP_ON_ERROR
Instructs BACKUP to fail if a checksum does not verify. (This is the default behavior.)
Checksums during RESTORE and RESTORE VERIFYONLY
If backup checksums are present on the backup media, by default, both the RESTORE and RESTORE VERIFYONLY operations verify the backup checksums and page checksums. If there is no backup checksum, either restore operation proceeds without any verification; this is because without a backup checksum, restore cannot reliably verify page checksums.
Two options, CHECKSUM and NO_CHECKSUM, allow you to modify how RESTORE and RESTORE VERIFYONLY handle checksum verification, as follows:
CHECKSUM
If you explicitly request CHECKSUM for a restore operation and if the backup contains backup checksums, backup checksums and page checksums are both verified, as in the default case. However, if the backup set lacks backup checksums, the restore operation fails with a message indicating that checksums are not present.
NO_CHECKSUM
Explicitly disables the default validation of any checksums by the restore operation.
Controlling the Response to an Error
To specify how a restore operation behaves on encountering an error, use the following RESTORE and RESTORE VERIFYONLY options:
CONTINUE_AFTER_ERROR
Specifies that the restore operation is to continue after an error is encountered. This is the default behavior for RESTORE VERIFYONLY, which lets it report validation errors and continue, providing as much information about the backup set as possible. CONTINUE_AFTER_ERROR tells RESTORE to continue as best it can. Among other things, this causes RESTORE to skip over invalid checksum.
STOP_ON_ERROR
Specifies that the restore operation stops and fails with the first error encountered. This is the default behavior for RESTORE.