A script to verify a database backup


Today, I will be sharing a very small, but important script. Recently, one of the database backups we had received failed to restore. I was faced with a problem of determining whether the problem was with the backup itself, or whether it was an I/O subsystem issue or some other failure.

Like with all tools & utilities, SQL Server provides great options when used via commands instead of the UI. Similarly, the RESTORE command provides the facility to very easily validate a backup for you. Please find below the script I used to validate my backup and was able to determine that the backup received was indeed, corrupt.

USE MASTER
-- Add a new backup device
-- Ensure that the SQL Server can read from the physical location where the backup is placed
--                    TYPE      NAME		   PHYSICAL LOCATION
EXEC SP_ADDUMPDEVICE 'disk','networkdrive','\VPCW2K8Database BackupTest.bak'

-- Execute the Restore operation in VERIFY ONLY mode
-- Provide the actual paths where you plan to restore the database.
-- This is because VERIFYONLY also checks for available space
RESTORE
VERIFYONLY
FROM  networkdrive
WITH
MOVE N'TESTDB_DATA'    TO N'E:TestDBTestDB_Data.mdf',  
MOVE N'TESTDB_INDEXES' TO N'E:TestDBTestDB_Idx.mdf',  
MOVE N'TESTDB_LOG'     TO N'E:TestDBTestDB_LOG.ldf'

-- DROP THE DEVICE
--                   Name         , Physical File (OPTIONAL - if present, the file is deleted)
EXEC SP_DROPDEVICE 'networkdrive'

The checks performed by RESTORE VERIFYONLY include (per Books On Line):

  • That the backup set is complete and all volumes are readable
  • Some header fields of database pages, such as the page ID (as if it were about to write the data)
  • Checksum (if present on the media)
  • Checking for sufficient space on destination devices

What methods do you use to validate your backups? Do leave a small note as your comments.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

5 thoughts on “A script to verify a database backup

  1. Nakul Vachhrajani

    Hello!

    Thank-you, Sankar for your feedback. I will attempt to bring even better quality posts in the future.

    As far as verifying a restore is concerned, I agree with you that performing an actual restore is the best option. However, the VERIFYONLY is a good “first-pass” to make sure that at least the backup media and other I/O issues are not present. The overall structure of the backup looks fine to the SQL Server, and after that it is definitely worth checking out the logical pieces by preforming the actual restore.

    Thanks & Regards,

    Nakul Vachhrajani.

    *Be courteous. Drive responsibly.*

    Like

    Reply
  2. Sankar

    Hi Nakul,

    Good work on the blogging front recently. The way I verify my backups is by doing a RESTORE. There is no other good & comprehensive way of verifying the backups without doing the actual restore. Although RESTORE VERIFY does work to some extent, IMO its still NOT a reliable way.

    Doing an actual RETSORE also helps to make sure you have the DR scripts handy and also helps to offload the DBCC CHECKDB work on the production box. I wrote about this a while ago here.
    [http://sankarreddy.com/2011/02/t-sql-tuesday-15-my-best-automation-job-ever-as-a-dba/][1]

    [1]: http://sankarreddy.com/2011/02/t-sql-tuesday-15-my-best-automation-job-ever-as-a-dba/

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s