Automate SQL Server RESTORE VERIFYONLY Process with Maintenance Tasks

By:   |   Comments   |   Related: > Maintenance


No doubt you likely have a monstrous database infrastructure to manage and the tasks required to keep it maintained and healthy can be daunting. For that reason those tasks should not be performed all in one sitting, nor during peak-hours of use. As a general rule, maintenance tasks should be performed during down times or periods of relative inactivity, which likely means you aren't sitting in the office when it needs to be done.

Taking time to think about maintenance strategy and dividing up the tasks into smaller, manageable chunks saves resources on your server and allows for reaction to issues that arise. The first and most important aspect of database maintenance is the backup and restore strategy. To make sure your backup files are readable you can create a procedure to test each file and email the results of unsuccessful tests to whomever needs the information.


You must make sure all databases and transaction logs are backed up in a way that provides for minimal recovery time, yet it's difficult to know if those files are even readable without performing some kind of test. In a previous tip we discussed the RESTORE VERIFYONLY statement as a way of determining the readability of backup files. You can create a stored procedure that loops through the backup devices and runs the RESTORE VERIFYONLY statement against each one. In this case we created a table that houses the results of each test. Once the loop testing completes the results can be obtained and included in an email. The procedure could be written this way:

SQL Server 2005

Cursor to iterate and test all backup files in SQL Server 2005

SQL Server 2000

Cursor to iterate and test all backup files in SQL Server 2000

Note that this procedure is written based on the fact that backup devices are used. You can modify the script to look for all backups that are performed straight to disk as well.

This type of procedure should be run weekly to make sure backup files are not corrupting, but should be done during non-peak hours, since the verification process takes time and resources. Remember, however, that RESTORE VERIFYONLY checks only the readability of the file, not necessarily the integrity of the file. In order to test the restore process you should perform an actual restore of the data. This should take place at least quarterly using a non-production server. Testing the actual restore process will provide you with an estimated time required for recovery, as well as prepare you for any issues that may arise during the restore process.

Next Steps

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

get free sql tips
agree to terms