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.