Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Nakul Vachhrajani is a Technical Specialist & Systems development professional with iGATE. He holds a MCTS (SQL Server 2008: Implementation & Maintenance)
Browse by Tags · View All
#SQLServer 305
SQL Server 304
Administration 252
DBA 241
T-SQL 234
#TSQL 232
Development 226
Tips 216
Guidance 148
Best Practices 119

Archive · View All
April 2011 14
March 2012 11
December 2011 11
March 2011 11
December 2012 10
October 2011 10
January 2011 10
September 2013 9
January 2013 9
November 2012 9

A script to verify a database backup

Apr 18 2011 1:00AM by Nakul Vachhrajani   

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.

-- 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
FROM  networkdrive
MOVE N'TESTDB_DATA'    TO N'E:\TestDB\TestDB_Data.mdf',  

--                   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.

Tags: Development, Administration, T-SQL, Tips, SQL Server, #SQLServer, #TSQL, Best Practices, DBA, SSMS, Tools and Utilities,

Nakul Vachhrajani
4 · 36% · 11648



  • 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. [][1]

    commented on May 9 2011 11:32AM
    115 · 1% · 454
  • 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.

    commented on May 9 2011 11:57PM
    Nakul Vachhrajani
    4 · 36% · 11648
  • 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 you may find interesting

    commented on May 10 2011 9:30AM
    Javin Paul
    1808 · 0% · 11
  • Thank-you, javabuddy for sharing, and am happy that you enjoyed reading the post.

    commented on May 10 2011 11:28AM
    Nakul Vachhrajani
    4 · 36% · 11648
  • I liked this blog as well. Thanks.

    commented on May 11 2011 10:14PM
    31 · 6% · 1776

Your Comment

Sign Up or Login to post a comment.

"A script to verify a database backup" rated 5 out of 5 by 1 readers
A script to verify a database backup , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]