Check your backups with a Restore Verify Only

Posted: August 4, 2008 in Uncategorized
Tags:

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.

Comments
  1. chrissie1 says:

    No problem. I moved the blog to lessthandot. Where we also have Denis Gobo (known as Denis the SQL menace) as a regular poster. A ver respected SQL server person😉.

  2. john says:

    Do you have any idea how common this is for a SQL backup to not be valid? Our company installs software at hospitals that includes a SQL Server DB. The DB gets backed up every day. That would be a bummer if the hard drive crashed and then the backups were corrupted, so that all data was lost. Is this something that we should be checking for at all of our sites, or is it very rare?

    thanks

  3. chrisshaw says:

    John,

    A lot is going to depend on the version of SQL Server. Version that were older then 2000, I would be checking often if we had a good backup. I have been working with 2000 for some time now over a number of servers. I think I have run into only one backup that was not good. So if you are newer then 2000 I think the chance is not very high.

    But by the way… there may be more pieces to the puzzle then just the SQL Server backup. You are going to want to run tests on a regular basis no matter what, I just talked to someone not to long ago, I believe Brent Ozar or Kevin Kline who said he knows a couple of shops that don’t even take the backs up to tape ever, now its a SLA from my understanding. But something to think about.

    No matter what the final destination is of the backup, lets say its an off site storage facility and its on tape you are going to want to make sure that those backups remain good. Backups now are a many step process and I believe each step should be tested for the ability to recover, how long it takes, and the number of people that it takes to recover.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s