© 2005 - 2012 Yohz Software, a division of Yohz Ventures Sdn Bhd
Products > SQL Verify > Overview
Overview Details Why RESTORE VERIFYONLY isn’t reliable Download Purchase Help file
You regularly back up your SQL Server databases, but do you worry if those backups can be restored?
You copy your backup files off-site, do you also worry if those copies can be restored, as file corruption may occur
during the transfer?
You have a server storing only valid backup files, but do you worry if those backups are still valid tomorrow, next week, or
next month, as your disks may experience IO failures over time?
You use RESTORE VERIFYONLY to quickly verify your backup files, but do you know how (un)reliable that verification
really is?
Backups are not enough!
Backups, while essential, are not the be all and end all of a disaster recovery plan. You need to ensure that you can restore from those
backup files. Nothing is worse than trying to restore from a backup set that is unrecoverable. At 2 a.m. in the morning!
RESTORE VERIFYONLY is rubbish!
SQL Server maintenance plans provide you the option to verify backup files, using the RESTORE VERIFYONLY command. Do
you know that there are instances where this verification can pass, but when it comes to restoring from those files, the restore
can fail and you get messages like this:
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: 51
Process ID: 316
Description: Invalid switch value
Connection Broken
Why does this happen? It has to do with the structure of a SQL Server backup file, and no, backup checksums are useless in these situations. We have a
test case you can use to prove to yourself that RESTORE VERIFYONLY isn’t reliable enough to bet your career on.
RESTORE DATABASE is a must!
Restoring from a backup set is really the only way to ensure that your backup files are recoverable. Compounding this issue is the fact
that every time you make a copy of your backup file, there is a risk that the file is incomplete or corrupted without any errors being raised
by the transferring application.
What if you copy your backup files off-site for redundancy? Does this mean that you need to restore from those backup files too to
ensure that the backup files are recoverable? What if you make multiple copies of your backup files? Do you need to restore from each
of those copies too?
Yes!
Every time you copy a file, or upload/download a file via FTP, there is a risk of undetected file corruption. It would then be very troublesome to
have to restore each and every backup file every time a copy is made. In addition, you will need to have SQL Server instances installed on
each and every machine that holds copies of the backup files.
File checksums to the rescue
Here is a what we’re suggesting. If you first generate a checksum of the backup file, and then successfully restore it, it stands to reason
that you can use the file checksum in the future to quickly ensure that any future copies of the backup files are consistent with the original
copy. Furthermore, you don’t need to maintain expensive SQL Server installations just to generate and compare checksums.
The solution, SQL Verify
That’s what we created SQL Verify for. It is an application designed to generate the file checksums for you, restore your
backup files, even run database consistency checks if you so require, copy your backup files to remote shares, upload them
to FTP servers, compare new file checksums against previously generated checksums, send e-mail and Windows message
alerts on errors, and much more. In short, it helps you ensure that your backup are usable, 24x7.
If you would like to see how SQL Verify can be used, here are some common scenarios that SQL Verify can be used in.
If you would like to learn more about SQL Verify, this page provides more details and includes actual screenshots from the application.
You can also download the help file, so that you can browse through our documentation without having to install anything. We have even posted the
help file on-line in HTML format, so just browse through that if you don’t want to download anything just yet.
Finally, if there is anything at all you would like to ask, please do contact us.
But it’s all hard work
You’ll need some sort of job scheduling system to periodically detect new backup files. The job will need to generate
checksums for those files. It’ll also need to be able to automatically restore from the backup files. On file servers, the
job will also need to be able to compute and compare checksums against known good values. The system will also
need to be able to alert you to inconsistencies and errors in any of the above processes.
Download a trial of SQL Verify now and experience
how it works to constantly ensure the reliability and
integrity of your SQL Server backup files.
Products > SQL Verify > Overview
Overview Details Why RESTORE VERIFYONLY isn’t reliable Download Purchase Help file
You regularly back up your SQL Server databases, but do you worry if those backups can be restored?
You copy your backup files off-site, do you also worry if those copies can be restored, as file corruption may occur
during the transfer?
You have a server storing only valid backup files, but do you worry if those backups are still valid tomorrow, next week, or
next month, as your disks may experience IO failures over time?
You use RESTORE VERIFYONLY to quickly verify your backup files, but do you know how (un)reliable that verification
really is?
Backups are not enough!
Backups, while essential, are not the be all and end all of a disaster recovery plan. You need to ensure that you can restore from those
backup files. Nothing is worse than trying to restore from a backup set that is unrecoverable. At 2 a.m. in the morning!
RESTORE VERIFYONLY is rubbish!
SQL Server maintenance plans provide you the option to verify backup files, using the RESTORE VERIFYONLY command. Do
you know that there are instances where this verification can pass, but when it comes to restoring from those files, the restore
can fail and you get messages like this:
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: 51
Process ID: 316
Description: Invalid switch value
Connection Broken
Why does this happen? It has to do with the structure of a SQL Server backup file, and no, backup checksums are useless in these situations. We have a
test case you can use to prove to yourself that RESTORE VERIFYONLY isn’t reliable enough to bet your career on.
RESTORE DATABASE is a must!
Restoring from a backup set is really the only way to ensure that your backup files are recoverable. Compounding this issue is the fact
that every time you make a copy of your backup file, there is a risk that the file is incomplete or corrupted without any errors being raised
by the transferring application.
What if you copy your backup files off-site for redundancy? Does this mean that you need to restore from those backup files too to
ensure that the backup files are recoverable? What if you make multiple copies of your backup files? Do you need to restore from each
of those copies too?
Yes!
Every time you copy a file, or upload/download a file via FTP, there is a risk of undetected file corruption. It would then be very troublesome to
have to restore each and every backup file every time a copy is made. In addition, you will need to have SQL Server instances installed on
each and every machine that holds copies of the backup files.
File checksums to the rescue
Here is a what we’re suggesting. If you first generate a checksum of the backup file, and then successfully restore it, it stands to reason
that you can use the file checksum in the future to quickly ensure that any future copies of the backup files are consistent with the original
copy. Furthermore, you don’t need to maintain expensive SQL Server installations just to generate and compare checksums.
The solution, SQL Verify
That’s what we created SQL Verify for. It is an application designed to generate the file checksums for you, restore your
backup files, even run database consistency checks if you so require, copy your backup files to remote shares, upload them
to FTP servers, compare new file checksums against previously generated checksums, send e-mail and Windows message
alerts on errors, and much more. In short, it helps you ensure that your backup are usable, 24x7.
If you would like to see how SQL Verify can be used, here are some common scenarios that SQL Verify can be used in.
If you would like to learn more about SQL Verify, this page provides more details and includes actual screenshots from the application.
You can also download the help file, so that you can browse through our documentation without having to install anything. We have even posted the
help file on-line in HTML format, so just browse through that if you don’t want to download anything just yet.
Finally, if there is anything at all you would like to ask, please do contact us.
But it’s all hard work
You’ll need some sort of job scheduling system to periodically detect new backup files. The job will need to generate
checksums for those files. It’ll also need to be able to automatically restore from the backup files. On file servers, the
job will also need to be able to compute and compare checksums against known good values. The system will also
need to be able to alert you to inconsistencies and errors in any of the above processes.
Download a trial of SQL Verify now and experience
how it works to constantly ensure the reliability and
integrity of your SQL Server backup files.