Grant Fritchey (12/30/2010)
Just so I can learn better, do you have any white papers or deeper documentation into precisely that issue available? I've got a little best-practice article I want to write up and having a good reference would really help.
Sorry, I don't have any white paper available. It's all been part of some research into how best to validate a backup file, for an upcoming product. It'll be included in the help file for that product, but that isn't publicly available yet.
It all started when we realised that SQL Server 2005 Express could verify backup sets larger than 4 GB. Yes, there is a limit (4 GB in SQL 2005, 10 GB in SQL 2008 R2) when restoring a database, but you could run RESTORE VERIFYONLY on backup files of any size. That of course opened up the possibility of setting up a dedicated machine running only the Express edition (for free) to validate backup files of any size. The backup sets would need to have been created with the CHECKSUM option so that the verification process can catch any corruption to the database data.
Unfortunately, we found out that while RESTORE VERIFYONLY checks the backup data just fine, it did not fully check the MTF parts of the backup file. In a backup file, there is first the MTF header, followed by the backup data, then followed by the MTF footer. Corruption to certain parts of the MTF data can still pass the verification process, but fail during the restore. You can download an archive here
that demonstrates this.
The original backup file is 'restoretest.bak', created with the CHECKSUM option on SQL 2005, that passes verification and restore. I changed a single byte in the backup data, and renamed it 'restoretest_verifyfail.bak', and that fails verification. Using the original backup file, I then changed a single byte in the MTF data and renamed it 'restoretest_verifypass_restorefail.bak', that passes verification but fails the restore with the following message on SQL 2005:
Server: Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Process ID: 316
Description: Invalid switch value
By all accounts, that is a scary message to see during a restore, more so when you have previously successfully verified the backup file.
So as you mentioned, the only sure way to ensure that your backup file is recoverable is to restore it. If you didn't use the CHECKSUM option during the backup, or is not available (e.g. using SQL 2000), best to run DBCC CHECKDB on the restored database too.
Get a SQL Size 2.0 single-instance license for FREEClaim one here!