SQL: How to test a SQL Backup

When reviewing disaster recovery processes I often get asked how a verify / test that a SQL Backup is “good”. Restoring the backup file is a solid and reliable way to verify the backup file, however it may not always be practical due to space and/or time restrictions.

During the backup task, you can check the “Verify backup when finished” reliability option to verify the backup set is complete and readable.

After the backup file has been create you can execute the following commands:

RESTORE VERIFYONLY FROM DISK=‘<your backup file name.bak>’
http://msdn.microsoft.com/en-us/library/ms188902.aspx
This option verifies the backup set is complete and readable, however it does not validate the data structure.

RESTORE HEADERONLY FROM DISK=<your backup file name.bak>’
http://msdn.microsoft.com/en-us/library/ms178536.aspx
Retrieve the header information of the backup set.

RESTORE FILELISTONLY FROM DISK=‘<your backup file name.bak>’
http://msdn.microsoft.com/en-us/library/ms173778.aspx
Returns a list of files in the backup set.

There are options available to test the integrity of the backup file, however you should periodically test your disaster recovery / business continuity plan. It is important to verify that the contents of your “backup” contains the pertinent data for a proper recovery.

Leave a Comment