Validating SQL Server Backups

You have your SQL Server Backup Plan and your Database Recovery Model set.  How do you know if your Backups are good? TEST!  Validating SQL Server Backups will ensure that you are in a good place when it is time to bring your database back from the dead!  

Don’t assume that your Backups are solid and let them sit on a shelf.Corrupt backups are recoverable, but worthless. Did we mention you can automate SQL Server Backup validation?

There are several methods for validating your Backups.

• • RESTORE – The most effective way to validate that your backups are good is to run a test Restore.  If your Restore is successful, you have a solid backup.  Make sure to run a test restore on your Full, Differential, Point in Time, and Transaction Logs! Bonus points if you automate refreshing non-production.

• Backup with CHECKSUM -It may not be realistic to run regular test restores on every single database, this is where CHECKSUM is your friend.  CHECKSUM is part of a backup operation which will instruct SQL Server to test each page being backed up with its corresponding checksum, making sure that no corruption has occurred during the read/write process. If a bad checksum is found, the backup will fail.  If the backup completes successfully, there are no broken page checksum
  • BEWARE though, this does not ensure that the database is corruption free, CHECKSUM only verifies that we are not backing up an already-corrupt database. (Later in this post we discuss checking data for corruption.) If it seems like too much trouble to write a CHECKSUM script every time you want to perform a backup, keep in mind that these can be automated as SQL Agent Jobs! A sample T-SQL script for using CHECKSUM is as follows:


Backup Database TestDB
To Disk='G:DBABackupsTestDBFull_MMDDYYYY.bak'
With CheckSum;

• • VERIFY – It is not wise to rely solely on CHECKSUM, a good addition is to use RESTORE VERIFYONLY.  This will verify the backup header, and also that the backup file is readable.  Note that much like CHECKSUM, this will check to see if there are errors during the read/write process of the backup; however, it will not verify that the data itself is valid or not corrupt.  Despite the name “RESTORE VERIFONLY”, it does not actually restore the data. VERIFY too can be automated to perform each time your backups utilizing CHECKSUM run.

• CHECKSUM on Restore –  Databases where BACKUP WITH CHECKSUM have been performed can then be additionally verified as part of the restore process. This will check data pages contained in the backup file and compare it against the CHECKSUM used during the backup. Additionally, if available, the page checksum can be verified as well. If they match, you have a winner…
  More Details on CHECKSUM and BACKUP CHECKSUM


Restore Database TestDB;
From Disk='G:DBABackupsTestDBFull_MMDDYYYY.bak'
With VerifyOnly;

Data Validation Prior to Taking Backups

Keep in mind that if your data is corrupt prior to a backup, SQL Server can BACKUP that CORRUPTED DATA.  The validation methods mentioned above guard you against corruption occurring during backups, not against corrupted data within the backup.  For data validation prior to backups being run, it is suggested that DBCC CHECKDB be performed on each database on a regular basis.

• DBCC CHECKDB –  SQL Server is very forgiving and will usually backup and restore corrupted data. A best practice is to run a DBCC CHECKDB on your data to check for potential corruption. Running CHECKDB regularly against your production databases will detect corruption quickly.  Thus providing a better chance to recover valid data from a backup, or being able to repair the corruption. CHECKDB will check the logical and physical integrity of the database by running these three primary checks*:
  • • CHECKALLOC – checks the consistency of the database;
  • • CHECKTABLE – checks the pages and structures of the table or indexed view; and
  • CHECKCATALOG – checks catalog consistency.

Automate Validation Steps

Corruption can happen at any time, most of the time it is related to a hardware issue.  Automating the steps necessary to validate your data and backups will help ensure you have the best practices in place to efficiently recover from catastrophic data loss.  Being able to backup and restore is not as important as being able to recover with valid data.Despite the above keys for validation, the only true way to verify that your backups are valid is to actually restore the database. It bears repeating: corrupt backups are recoverable, but worthless. 

*A full list of DBCC CHECKDB checks can be found here.