| |
 |
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.
 Acknowledgement: Some icons on this page
were generously provided by
Fasticon.com.
Discuss or comment on this article on our Facebook group.
Document history
| 6/27/2008 | Initial release. |
|
