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.
SQLTwins by Nakul Vachhrajani 
Thank-you, javabuddy for sharing, and am happy that you enjoyed reading the post.
LikeLike
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.*
LikeLike
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/
LikeLike
cool stuff man, thanks for sharing this nice utility script, its indeed useful . I have also blogger some of MySQL experience as [top 10 mysql commands for beginners][1]
you may find interesting
[1]: http://javarevisited.blogspot.com/2010/10/frequently-used-mysql-commands-part-3.html
LikeLike
I liked this blog as well. Thanks.
LikeLike