Verifying Backups with the RESTORE VERIFYONLY Statement

By:   |   Comments (2)   |   Related: > Backup


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.


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
  • More information on Backup and Restore operations.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author MSSQLTips was started in 2006 to provide SQL Server content about various aspects of SQL Server and other database platforms.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

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



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


--- MEDIA set,software name, software vendoreid

--- CHECK MDF ,NDF AND log files with size

-- CHECK Backup present in compatibility level, software version major,software version major

get free sql tips
agree to terms