By: MSSQL Tips | Updated: 2006-10-20 | Comments (2) | Related: > 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.
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
- More information on Backup and Restore operations.
About the author
MSSQLTips.com 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
Article Last Updated: 2006-10-20
