Verifying Backups with the RESTORE VERIFYONLY Statement

By: Edgewood Solutions | Read Comments | Print Edgewood Solutions is a technology company focused on Microsoft SQL Server and founder of MSSQLTips.com. Related Tips: 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.

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

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.

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:

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

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:

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:

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):

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.

Related Tips: More | Become a paid author

Last Update: 10/20/2006

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Try the award winning SQL diagnostic manager as a free 14-day trial! Time-strapped database professionals use SQL Monitor to look after their servers. Try it online. SQL Servers keeping you up at night? Contact expert SQL Server consultants for a Health Check. Find and Fix SQL issues with Foglight Performance Analysis. Get a free copy. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. The SQL Server Security THREAT - It’s Closer Than You Think