Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Automate SQL Server RESTORE VERIFYONLY Process with Maintenance Tasks


By:   |   Read Comments   |   Related Tips: More > Maintenance

Attend a SQL Server Conference for FREE >> click to learn more


Problem
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.

Solution
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



Last Update:


signup button

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools