© 2005 - 2012 Yohz Software, a division of Yohz Ventures Sdn Bhd
Products > SQL Verify > Why RESTORE VERIFYONLY isn’t reliable
Overview Details Why RESTORE VERIFYONLY isn’t reliable Download Purchase Help file
Most SQL Server DBAs will verify their backup sets using the RESTORE VERIFYONLY command to gain some assurance that their backup sets are usable.
Unfortunately, RESTORE VERIFYONLY isn’t reliable enough.
You can have a backup set that verifies just fine, but when it comes to actually restoring it, it might still fail. Take these 2 backup files. File A will pass the
verification test and restore without errors. File B, which is a copy of File A except for a change in a single byte, will pass verification but fail to restore. In
fact, when you attempt to restore it, SQL Server raises the following error:
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.
Location: restore.cpp:4440
Expression: FALSE
SPID: 53
Process ID: 1396
Description: Invalid switch value
Connection Broken
You can download the files and try it yourself on SQL Server 2005, 2008 and 2008 R2. So what went wrong? Why did SQL Server report that the backup
set is valid when it verified it, but restoring it results in a failure?
The reason lies in how a backup set is stored. Backup sets are stored using the Microsoft Tape Format (MTF) structure. In a backup file, there are blocks of
MTF data, followed by the backup data, followed by more blocks of MTF data. When SQL Server verifies a backup set, it does not verify the MTF data
thoroughly. Unfortunately, during a restore, SQL Server requires more of the MTF data to be valid than what it actually verified. Thus, if your MTF data is
corrupted, you may be in for a surprise if you rely only on RESTORE VERIFYONLY to validate your backup sets.
The only way to ensure your backup sets are valid is to restore them. It’s not easy, nor convenient, nor cheap, but necessary. Every backup set has to be
restored to ensure they are valid. The same goes for every copy you make of a backup set. You would probably end up with installing SQL Server on every
file server you use to keep copies of your backup sets.
Which is why we created SQL Verify. SQL Verify is designed so that you only need to restore your backup sets once, and thereafter, you can verify them
anytime and anywhere without having SQL Server installed. This cuts down on the number of SQL Server licenses you need to purchase, and makes
verification of the backup sets, or copies of the backup sets, so much more simpler.
You can read more about SQL Verify on this product details page.
Products > SQL Verify > Why RESTORE VERIFYONLY isn’t reliable
Overview Details Why RESTORE VERIFYONLY isn’t reliable Download Purchase Help file
Most SQL Server DBAs will verify their backup sets using the RESTORE VERIFYONLY command to gain some assurance that their backup sets are usable.
Unfortunately, RESTORE VERIFYONLY isn’t reliable enough.
You can have a backup set that verifies just fine, but when it comes to actually restoring it, it might still fail. Take these 2 backup files. File A will pass the
verification test and restore without errors. File B, which is a copy of File A except for a change in a single byte, will pass verification but fail to restore. In
fact, when you attempt to restore it, SQL Server raises the following error:
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.
Location: restore.cpp:4440
Expression: FALSE
SPID: 53
Process ID: 1396
Description: Invalid switch value
Connection Broken
You can download the files and try it yourself on SQL Server 2005, 2008 and 2008 R2. So what went wrong? Why did SQL Server report that the backup
set is valid when it verified it, but restoring it results in a failure?
The reason lies in how a backup set is stored. Backup sets are stored using the Microsoft Tape Format (MTF) structure. In a backup file, there are blocks of
MTF data, followed by the backup data, followed by more blocks of MTF data. When SQL Server verifies a backup set, it does not verify the MTF data
thoroughly. Unfortunately, during a restore, SQL Server requires more of the MTF data to be valid than what it actually verified. Thus, if your MTF data is
corrupted, you may be in for a surprise if you rely only on RESTORE VERIFYONLY to validate your backup sets.
The only way to ensure your backup sets are valid is to restore them. It’s not easy, nor convenient, nor cheap, but necessary. Every backup set has to be
restored to ensure they are valid. The same goes for every copy you make of a backup set. You would probably end up with installing SQL Server on every
file server you use to keep copies of your backup sets.
Which is why we created SQL Verify. SQL Verify is designed so that you only need to restore your backup sets once, and thereafter, you can verify them
anytime and anywhere without having SQL Server installed. This cuts down on the number of SQL Server licenses you need to purchase, and makes
verification of the backup sets, or copies of the backup sets, so much more simpler.
You can read more about SQL Verify on this product details page.