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','\\VPCW2K8\Database Backup\Test.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:\TestDB\TestDB_Data.mdf',
MOVE N'TESTDB_INDEXES' TO N'E:\TestDB\TestDB_Idx.mdf',
MOVE N'TESTDB_LOG' TO N'E:\TestDB\TestDB_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.