Responding to SQL Server Restore Errors Caused by Damaged Backups
Restore errors occur if the backup media is damaged. Restore errors may be reported by the operating system or detected by checksums. In either case, you have three options:
- Cure the error and restart the restore operation.
- Allow the restore to continue despite errors, and repair the database after the restore completes.
- Abandon the restore operation, and use an alternate recovery plan that avoids the damaged backup.
Note
The media set or the backup set must contain minimal correct information for it to be interpreted as Microsoft Tape Format. If this is not the case, RESTORE stops and indicates that the format of the backup is invalid.
Curing and Restarting the Restore Operation
Errors may be cured in the following ways:
- If an error occurred on a tape device, you can clean or replace the tape drive.
- For disk devices, you can resolve the device error and replace the damaged file.
- If a media set is mirrored, you can replace the damaged media with the corresponding media from another mirror.
Continuing Despite Errors
Warning
Specifying WITH CONTINUE_AFTER_ERROR in a RESTORE statement attempts to restore the database. However, there are many kinds of corruption that prevent recovering a database. We strongly recommend that you reserve using the CONTINUE_AFTER_ERROR option until you have exhausted all alternatives.
The CONTINUE_AFTER_ERROR option causes a restore operation to continue past errors, restoring what it can. Roll forward occurs and you can apply subsequent transaction log backups. If roll forward encounters an error that prevents it from reaching the target point in time, this error is indicated in the log. At the recovery point, the database is brought online, if it can be. But if recovery cannot be completed, the database is left offline.
The amount of data lost depends on the error encountered. For example, a bad checksum on a page causes only that page to be questioned; the media continues to be read and processed. In contrast, an I/O error reported from a tape device might prevent restore from reading past the error, preventing the remainder of the tape from being restored.
When a restore is instructed to continue after errors, pages that fail verification are written to disk and logged in the suspect_pages table and the error log.
Best Practices: After you use WITH CONTINUE_AFTER_ERROR to restore data, examine the error logs for details on the errors. Also, save and analyze all messages that you get directly from the RESTORE statement.
To continue despite errors
- The basic RESTORE syntax is:
- RESTORE DATABASE database_name FROM backup_device WITH CONTINUE_AFTER_ERROR, [ NORECOVERY ]
Managing the Offline Database
At the end of a restore sequence that continues despite errors, you may be able to repair the database with DBCC CHECKDB. For CHECKDB to run most consistently after using RESTORE CONTINUE_AFTER_ERROR, we recommend that you use the WITH TABLOCK option in your DBCC CHECKDB command. For more information, see DBCC CHECKDB (Transact-SQL). All repair options are available. To learn the minimum repair level needed, run DBCC CHECKDB without a repair option. Note that, in extreme cases, there may not be enough information to repair the database.
To gain limited access to the data as-is, you can place the database into emergency mode using the EMERGENCY option of the ALTER DATABASE command.
See Also
Concepts
Understanding and Managing the suspect_pages Table
Other Resources
ALTER DATABASE (Transact-SQL)
BACKUP (Transact-SQL)
DBCC CHECKDB (Transact-SQL)
RESTORE (Transact-SQL)
RESTORE VERIFYONLY (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|