Verifying backup files

Prev Page Next Page
Recovery models
Main backup types
Backing up the database files by copying
The transaction log
Transaction log restore sequence
Log sequence numbers
Truncating and shrinking the transaction log
Backing up the tail
Inside the transaction log
So, what's in a backup file?
Test: A full backup does not contain deleted data
Verifying backup files
Verifying backup files on a budget
Cumulative backups
Recovering individual tables
Backup and restore history details
Backup reads and writes
Speeding up backups
Backup speed details
Speeding up restores
Restore state affects speed too
Backup and restore rights
Log shipping
Log shipping in SQL Server 2000
Setting up log shipping using Enterprise Manager
Checking the set up
Log shipping in SQL Server 2005
Setting up log shipping using Management Studio
Checking the set up
Log shipping status report
Log shipping in SQL Backup
Using the CopyTool utility
3rd party backup applications
VDI versions
VDI errors
SQL Backup - beyond compression
Restoring a chain of transaction log backups
Restoring to the latest possible state
Backing up multiple databases
Backup retention
Making a copy of the backup file
Backup file naming conventions
Restoring the latest backup set
Network resilience
Integrated database verification
Database file relocation
Improved backup retention
High-availability group support
Common SQL Backup issues
Installation checklist
Setting up rights
Configuring service rights
Backup data
Hanging issues
Common backup and restore errors
Error 3201 - when performing a backup to a network share
Full database backup file is larger than database size
Error 3205 - Too many backup devices specified for backup or restore
Error 4305 - an earlier transaction log backup is required
Bringing a database that is in recovery or read-only mode online
Using bulk-logged recovery model but transaction log backup is still large
Error 14274 - unable to delete SQL Server Agent job
Error messages when restoring from different versions of SQL Server.
vdi error codes
Restore speed details
Help, my transaction log file is huge!
Mirror or log ship

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]


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.

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

Document history
1/26/2011    Added note on RESTORE VERIFYONLY.    
6/27/2008    Initial release.    
Copyright 2008 - 2021 Yohz Ventures Sdn Bhd. All rights reserved.
All product and company names are trademarks or registered trademarks of their respective owners.