Automate SQL Server RESTORE VERIFYONLY Process with Maintenance Tasks

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

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

• Review your procedure for backup and restoration of databases and, if necessary, document the process
• Consider automating the process of testing the readability of the files to ensure backups are being adequately created
• Download these sample SQL Server 2000 and SQL Server 2005 scripts
• Review information on the RESTORE VERIFYONLY statement and Database Mail feature for SQL Server 2005
• Stayed tuned to MSSQLTIPS for more information on maintenance schedules and how to automate them
• Check out these tips on backup and recovery on MSSQLTIPS:
  • Verifying Backups with the RESTORE VERIFYONLY Statement
  • SQL Server 2000 to 2005 Crosswalk - Database Maintenance Plan Wizard to SQL Server Integration Services (SSIS)
  • COPY_ONLY Backups with SQL Server 2005
  • SQL Server 2005 Backup Product Options
  • Database Backup and Restore Failure Notifications
  • Backup to multiple files for faster and smaller backup files
  • Simple script to backup all databases

Related Tips: More | Become a paid author

Last Update: 10/31/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! SQL Monitor – For database professionals who need results on Day One. Try it online. SQL Servers keeping you up at night? Contact the Edgewood SQL Server DBAs for a Health Check. Prices start at $995. Free Trial: Get Proactive Insight with Spotlight® for SQL Server Enterprise. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Are you waiting on SQL Server? Learn about these DMV's.