Is it just me, or does there seem to be a lot of discussion these days about testing backups and having a good restore plan in place, before you need it.  You don’t want to be “testing” your backups at 3am during a critical restore operation.

Verify Backup Integrity (aka RESTORE VERIFYONLY)

So when it comes to testing backups, you might recall that there’s an option in the Backup Database maintenance plan task, Verify Backup Integrity.  And you might wonder whether checking this option is a good enough test.  Great question.

What the Verify Backup Integrity option does is tell SQL Server to perform a RESTORE DATABASE… VERIFYONLY once the backup is complete.  What exactly does RESTORE VERIFYONLY do?  Well, a few things:

• It makes sure that the backup media is complete and readable.
• It checks the backup header, to make certain it’s formatted like a backup header should be.
• It performs a checksum on the backup, if the backup was performed with the checksum option and the RESTORE VERIFYONLY command is executed with the checksum option.
• It makes sure there’s enough free space available on disk to perform a restore operation.

Basically, RESTORE VERIFYONLY is checking to see if there were any IO errors during the backup process.  Its goal is to come as close as it can to performing a restore, without actually restoring anything.

So what’s the problem?

But RESTORE VERIFYONLY by itself isn’t enough to ensure you can recover your database.  Why?  Because it doesn’t verify that the data being backed up is any good.  It doesn’t perform any sort of integrity check.  If the data itself is corrupt, it’s still possible to take a perfectly “good” backup that will go through a VERIFYONLY operation with no errors.

But what if you use the CHECKSUM option?  That will help check the data, right?  Not always.  First of all, backing up the database WITH CHECKSUM will create a checksum on the backup media.  RESTORE VERIFYONLY WITH CHECKSUM will use that backup checksum to verify the backup itself isn’t corrupt.  Now, in addition to creating the backup checksum, using the WITH CHECKSUM option when taking a backup will also validate page consistency using checksums or torn page detection, whichever, if any, is present.  But here’s the thing: if this database was upgraded from an earlier version of SQL Server, not all pages might have a checksum.  In that case, the backup process has nothing to validate, though it will continue to run and build its own backup checksum.

And let’s not forget the biggest question mark when it comes to backups and restores:  the human factor.  Running a RESTORE VERIFYONLY can’t tell you that you can’t restore to a point in time because you’re not doing log backups.  It can’t tell you whether your backups are making it to tape.

A better way

So what’s the answer?  First, you should be running integrity checks on your databases as often as your business rules and maintenance window will allow.  Second, Verify Backup Integrity or RESTORE VERIFYONLY is better than nothing as a sanity check on your backups, but a better option is to actually perform a full test restore somewhere and run an integrity check.  We’ve created a VM instance and automated job just for this purpose.  We also use it to run “fire drills”, requesting backups from tape and doing point in time restores.  Not only does it test the backups themselves, it also helps us test our entire backup and recovery process.  At 3pm, rather than 3am.