Check a SQL Server BAK file with RESTORE VERIFYONLY before you rely on it. Microsoft states that the command does not verify the structure of the data inside the backup. Use another backup that has no damaged pages when one exists. CONTINUE_AFTER_ERROR is the last resort and can salvage only part of the data.
Restore fails with an error and your database cannot come online. First decide whether the BAK file is damaged or the restore command is wrong.
Every step below follows the Microsoft documentation on RESTORE arguments and RESTORE VERIFYONLY. T-SQL samples here are not executed in a test instance.
How do You Check a BAK File Before Restoring?
RESTORE VERIFYONLY checks a backup without restoring it. Microsoft lists four checks: the backup set has no missing parts and all volumes are readable, some header fields of database pages like the page ID, the checksum when present on the media and enough space on the destination devices.
RESTORE VERIFYONLY FROM DISK = 'D:\Backups\Sales.bak' WITH CHECKSUM;Microsoft adds a limit. RESTORE VERIFYONLY does not attempt to verify the structure of the data inside the backup volumes. Passing the check does not prove the database restores cleanly.
What are CHECKSUM and CONTINUE_AFTER_ERROR for?
| Option | Behavior |
|---|---|
| CHECKSUM | Verifies backup checksums and fails the restore when the backup has none |
| NO_CHECKSUM | Disables checksum validation |
| STOP_ON_ERROR | Stops at the first error and is the default for RESTORE |
| CONTINUE_AFTER_ERROR | Continues after an error and is the default for VERIFYONLY |
On an invalid checksum RESTORE reports a checksum error and the page number then stops. With CONTINUE_AFTER_ERROR it proceeds when the corruption permits.
What if the Backup has Damaged Pages?
- Find an older backup taken before the damage. Microsoft calls this the best approach.
- Restore that backup and apply later log backups when you have them.
- Use CONTINUE_AFTER_ERROR only when no clean backup exists. Microsoft calls it a last resort to salvage data.
RESTORE DATABASE SalesCopy FROM DISK = 'D:\Backups\Sales.bak' WITH CONTINUE_AFTER_ERROR;Restore to a new database name first so the damaged data never replaces a good copy. For mirrored media Microsoft states you can replace a damaged volume with the matching volume from another mirror.
For system database restores see how to restore the master database. For basics see what Microsoft SQL Server is.
Frequently Asked Questions
Can RESTORE VERIFYONLY Prove a BAK File is Healthy?
No. Microsoft states it does not verify the structure of the data inside the backup.
What does RESTORE VERIFYONLY Check?
It checks that the backup set has no missing parts and volumes are readable and the checksum when present and the space on destination devices.
When should I Use CONTINUE_AFTER_ERROR?
Use it only when no clean backup exists. Microsoft calls it a last resort.
Does RESTORE Stop at the First Error?
Yes. STOP_ON_ERROR is the default for RESTORE except for VERIFYONLY.
Is an Older Backup Better Than Repair?
Yes. Microsoft states a backup taken before the damage is the best approach.
Key Takeaways
- RESTORE VERIFYONLY checks readability and checksums only.
- Passing the check does not prove data structure is sound.
- Older clean backups beat any repair option.
- CONTINUE_AFTER_ERROR is a last resort for partial salvage.
- Restore to a new database name first.
Sources and References
Steps follow the Microsoft documentation.
- RESTORE Statements Arguments (Microsoft Learn) learn.microsoft.com
- RESTORE VERIFYONLY (Microsoft Learn) learn.microsoft.com