One of the worst things you can ever encounter is having a backup file that cannot be restored.  I'm not taking about lack of disk space or things like that, but a 100%-certified corrupted backup file.  What can you do, you ask?  Well, SQL Server has this neat restore functionality to verify backup files.

Let's back up a little here.  When you first create a backup file, it should be good, 'should' being the operative word.  Every time this file is copied to another location, there is a risk of file corruption.  The best way to ensure that the file is still usable is to restore the backup itself, and run DBCC CHECKDB immediately.  If continuously restoring and running consistency checks is not feasible, using the RESTORE VERIFYONLY command is your next best option.

Unfortunately, there is a catch to this.  In SQL Server 2000, this is what happens during a RESTORE VERIFYONLY session:

Now, if you used a hex editor, and mangled up the backup data stored in the backup file, and ran RESTORE VERIFYONLY again, SQL Server will still tell you that 'The backup set is valid.'.

In SQL Server 2000, RESTORE VERIFYONLY only checks that the file conforms to the Microsoft Tape Format (MTF) specification, and can be read from disk.  Period.  Your backup data could contain junk for all it cares.

Things get worse.  In some cases, you will still be able to restore from that file, albeit with corrupted data.  The only time you can tell that something is wrong is when you run DBCC CHECKDB on the database, or when users start complaining (whichever comes first).

Now in SQL Server 2005, the default settings for backups has the same effect.  That's right, if you had a backup file that contained junk in the backup data area, SQL Server 2005 will still report that the backup set is valid.

So now, what can we do?  We need to use the CHECKSUM option for our backups e.g.

BACKUP DATABASE AdventureWorks TO DISK = 'G:\backups\AdventureWorks_full.bak' WITH CHECKSUM

Now, if you change even a single byte in the backup data area in the file, running a RESTORE VERIFYONLY on that file generates the following:

Server: Msg 3189, Level 16, State 1, Line 1 Damage to the backup set was detected. Server: Msg 3013, Level 16, State 1, Line 1 VERIFY DATABASE is terminating abnormally.

This is what happenned:

Finally!  A RESTORE VERIFYONLY that actually works the way users expect it to work.  Using the CHECKSUM option during the backup causes SQL Server to performs the following:

·compute a checksum for the backup data, which is then compared against the checksum during a RESTORE VERIFYONLY process

·verifies each page's checksum.  If the verification fails, the backup is aborted.  This is definitely a good way to ensure that you are backing up a 'good' database in the first place.

So, what's this going to cost you?

·a higher processor overhead during the backup and restore process [TODO real numbers]

·[TODO] a longer time to back up and restore [TODO real numbers]

Summary

Using the default backup settings in SQL Server 2000 and SQL Server 2005, RESTORE VERIFYONLY does not check if the data in the backup file is corrupted.  Verifying backup data integrity can only be done in SQL Server 2005, and only if the backup file was created using the CHECKSUM option.

UPDATE: Unfortunately, even RESTORE VERIFYONLY isn't 100% reliable.  See here for details.

Acknowledgement: Some icons on this page were generously provided by Fasticon.com.