mssqltips logo

Verifying Backups with the RESTORE VERIFYONLY Statement

By:   |   Updated: 2006-10-20   |   Comments (2)   |   Related: More > Backup

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 Updated: 2006-10-20


get scripts

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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools