I just found a help script that I thought I would post about. There is a blog located at http://blog.baesonline.com/2008/04/17/DoAutomatedRestoreTestsOnYourSQLBackups.aspx on April 17th the owner put up a script that you can place in a stored procedure and use it to validate your backs ups.
One of the concerns that I have that I speak a lot about is how to make sure that you daily checklist items are being completed. It’s something that I have spoken about at SSWUG.org, a couple of times. The short of it is that in our day to day life as a DBA is easy to forget or to set aside things that we should check everyday. Let me give you a great example:
At one point in my career I worked for a company that will remain unnamed. And we were using a 3rd party backup software. This software was designed for when you execute a backup you call it via extended stored procedures. When we set this up as a job we found something really interesting. The fact that the job called the stored procedure and the stored procedure would return a message would be considered a good execution. So unless we had the job to notify us of competed jobs, we would not see a failure when the third party product would return a message of backup failed. The job saw a return message so it considered it good to go. This blog entry that I linked to will allow you to do a restore with verify only and log the results in a table. The table code is included in the blog. This way you can take a look at the single table and see if the backups were good or bad. If you want to be really on top of the ball I imagine that you can either set a trigger or a job that will check for anything that has failed in it and send a notification, send a message to your MOM server is you are using one or any number of other monitoring tools.
Thanks for the code post Crissie.