How to verify SQL database backups

When disaster strikes, the only thing more frustrating than not having an up to date and relevant database backup is having a corrupt backup. When you first create a backup file, it should be good, with ‘should’ being the operative word. Every time the file is copied to another location, there is a risk of file corruption. A foolproof way to ensure that the file is still usable is to restore the backup itself, and run DBCC CHECKDB immediately against the newly restored SQL database.

Of course, continuously restoring and running consistency checks is not a really feasible long term strategy as each restore costs you time, CPU time an hard disk space. Luckily, there is a way to verify your SQL database backups which doesn’t include actually restoring them.

To check if your backup is valid execute the following SQL statement:

RESTORE VERIFYONLY

Utilize this statement against the SQL Server and the backup set you want to verify. However, depending on your disaster recovery strategy solely relying on RESTORE VERIFYONLY might not be enough. Namely, if your backups aren’t compressed or created using the CHECKSUM option, this command will ensure that the SQL Server is able to read the contents of the backup. The data stored within could be completely corrupt, and SQL Server will still report that the backup set is valid.

When you take a compressed backup or use the CHECKSUM option, SQL Server will calculate the checksum of each page that it backs up in addition to computing a checksum for the entire backup data. Therefore, running RESTORE VERIFYONLY on the backup set will result in SQL Server comparing both the backup page and data checksums against the checksums generated when the backup was taken. This ensures that the database pages are consistent as well as the backup data as a whole.

Does that mean that with page checksums and backup checksums present, running RESTORE VERIFYONLY is enough to guarantee that your backup set can be restored without problems?

Unfortunately, no.

Namely, when you create a backup file, the backup data is stored in a format known as the Microsoft Tape Format (MTF). The MTF blocks are used to store backup metadata, including the database name, files, size etc. The checksum we’ve discussed is computed only over the SQL Server backup data, and not for the MTF blocks. Thus, if the data in the MTF blocks is inconsistent or corrupted, you will be unable to restore the backup data, even though the backup data itself is consistent. RESTORE VERIFYONLY performs only some simple checks on the MTF blocks itself, and can still report that the backup set is valid even though the backup file cannot be restored.

So does that mean that you need to perform a full restore of each backup and run DBCC CHECKDB afterwards just to validate it?

Fortunately, no. This is where ApexSQL Restore comes into play.

ApexSQL Restore attaches both native and natively compressed SQL database and transaction log backups as live databases, accessible via SQL Server Management Studio, Visual Studio or any third party tool. It allows attaching single or multiple backup files including full, differential and transaction log backups, making it ideal for continuous integration environments, quickly reverting inadvertent or malicious changes and balancing heavy report loads. For more information, visit the ApexSQL Restore product page.

To verify a full database backup using ApexSQL Restore:

1. Start ApexSQL Restore
2. Click the Add virtual database button
3. In the Restore a backup dialog that will appear provide:
   1. The name of the SQL Server instance the backup will be attached to
   2. The preferred authentication method; to use SQL Server authentication a set of valid SQL Server credentials needs to be provided
   3. The name of the database the backup will be virtually restored to
4. Click Next
5. To specify the backup that will be attached click Add files button, navigate to the backup file and click Open

6. Review the backup properties and select the appropriate checkboxes next to each backup you want to attach

   

7. To attach a different file click Remove file and repeat the process described in step 5.
8. To specify a non-default restore path click Advanced >> and specify the path
9. To return database state to the exact point in time, click Specific point in time, and specify it on the timeline
10. Click Finish to attach the backup

And that’s all there is to ensuring your backups aren’t corrupted without actually restoring them.