Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Verifying Backups with the RESTORE VERIFYONLY Statement


By:   |   Read Comments (2)   |   Related Tips: More > Backup

Attend a SQL Server Conference for FREE >> click to learn more


Problem
There are a number of ways to make sure data in your organization is highly available, yet technology always seems to find a way of making data disappear. Data backups should be the cornerstone of every organization's disaster recovery plan. So, how do you know whether the backups are actually readable? One way is by issuing a RESTORE VERIFYONLY statement against the backup file.

Solution
When backing up database files in SQL Server 2000 and 2005, the RESTORE VERIFYONLY statement is available to verify that the backup is readable. This can be accomplished through either Enterprise Manager, SQL Server Management Studio (SSMS), T-SQL Statements, the OSQL Utility or other command line query tools.

Backing up a database using Enterprise Manager

Once you right-click the database and Choose Backup database a screen will appear so you can customize the backup:

General options when choosing to backup a database

Option to verify the backup once completed

When you choose to verify the backup in Enterprise Manager, a RESTORE VERIFYONLY statement is executed against the new backup file after the backup is complete.  Here is a Profiler trace that shows the backup and then the verifyonly statement.

Profiler view of Database backup using Enterprise Manager

In SQL Server 2005 a checksum function was added to the backup process, which performs a checksum on the data prior to writing the data to the backup file. This provides an extra level of data protection. Right-click the database, choose Tasks, then Backup:

Backing up a database in SQL Server 2005 Management Studio

Under the Reliability section is the option to Verify the backup after completed, as well as to perform a checksum:

Additional options for backing up the database

You can also use T-SQL commands to verify the readability of the backup file. Once a backup is created, whether through Enterprise Manager, SSMS, or T-SQL, you can issue the following command. If the file is readable then the following result is returned:

T-SQL statement for RESTORE VERIFYONLY with result

The OSQL utility can also be used to verify the readability of a backup file. Below is a screenshot of that process. A trusted connection is established to the server and the same command used in T-SQL is issued. As you can see the result issued from the server is the same as when running the command in Query Analyzer:

Using RESTORE VERIFYONLY in OSQL in a SQL Server 2000 instance

When issuing the statement using OSQL on a SQL Server 2005 instance the result issued by the server is a little different but essentially means the same thing (the result is the same in an SSMS query window):

Using RESTORE VERIFYONLY in OSQL in a SQL Server 2005 instance

Next Steps

  • Keep in mind that using the RESTORE VERIFYONLY statement is not a guarantee of reliable data restoration-it is merely to check the readability of the file. The only sure fire way of knowing if backups will accurately restore data is by performing test restores, which can be done on development servers to avoid service interruption
  • Review information on the RESTORE VERIFYONLY statement for SQL Server 2000 and SQL Server 2005
  • Go to MSSQLTIPS.COM for more information on Backup and Restore operations.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Edgewood Solutions Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, October 14, 2016 - 10:45:30 AM - Kris Maly Back To Top

 Awesome

 


Monday, August 19, 2013 - 12:24:27 AM - Dinesh Back To Top

Hi,

RESTORE LABELONLY FROM DISK='g:\TEST.BAK'
--- MEDIA set,software name, software vendoreid

RESTORE FILELISTONLY FROM DISK='g:\TEST.BAK'
--- CHECK MDF ,NDF AND log files with size


RESTORE HEADERONLY FROM DISK='g:\TEST.BAK'
-- CHECK Backup present in compatibility level, software version major,software version major


Learn more about SQL Server tools