Did You Restore It?

Posted: March 13, 2013 in Database Recovery, SQLServerPedia Syndication

Are you testing your backups, If so how? Sure there is a couple options that you can use to help validate backups but does this mean you are 100% sure without a doubt that it can be restored? Have you ever seen a backup file not restore?

Last week I was working on a server that was having a little bit of a storage issue, the vendor was pretty sure after we took a couple steps that the storage would return to normal behavior. Management was not satisfied with the vendors forecast of pretty sure, and as I side note I could not agree more with them. Management knew that a restore process was going to take some time, so rather than wait for the maintenance window when the storage fix was going to be attempted a decision was made to start a restore on a different server. This restore was being done as a just in case… Just in case the fix on the storage side did not work, and they lost a database. So I was off and running with a restore database task.

About two hours into the restore I received an error that stated unexpected end of file. I wasn’t too sure what exactly caused the short file, but I did know it did not sound good, and I could not restore from it. After I did some research it looked like the backup file I was restoring from was much smaller than the backup file SQL Server had created a few days before.

So here is my point…

It does not matter what you do on the backup side of the process, if you do not restore the file, you are at risk of having a bad backup file and then what?

It is good practice to restore your backups on a frequent basis, I may even be as bold as to say that you may want to restore each backup just to verify it. Either way, when you restore these backups it may not be good enough to copy the file to another server and then restore that backup. When you do a test restore you should test it from end to end. This means if you store your backups off-site then request a backup from that off site location and restore that file. If you restore your files with that methodology, not only are you testing that SQL Server created a good backup file, but that all the other pieces work as well. I like to back up and keep only one week’s worth of backups on local storage. As soon as the backup is done, it either goes to tape or another data center. So there are a number of pieces in that backup process, those are the same pieces that are part of the restore process.

Do yourself a favor, test your backups. Do this by restoring your databases from end to end.

  1. SQLGlenn says:

    Test backups. I wish I had enough storage to test my backups. I test so seldom it makes me ashamed to call myself a dBA. Same goes for DBCC checkdb. Living on the edge sucks.

    • Chris Shaw says:

      You know I have been there, ever consider really inexpensive storage such as external USB drives and such. I would think that anything is better than nothing.

  2. Chris Yates says:

    Current shop; run over 75 servers and 1200 or so DB’s. Spot checking of course these routinely would just be a random guess so I obtained a physical server. I took the largest backup we had to allow for space on that server than copy all my backups that currently go to storage (keep 30 days) over to the server and restore (all automated) if one fails alert goes out then it is removed from the test backup location and moves onto to the next backup. Once the backup is restored a check is done to ensure it is valid if not alert goes off.

    Fortunate enough to work for a shop that invests in the backups and for me automation is key.

    Nice post Chris

    • Chris Shaw says:

      Thanks for the comments, your process would make for a great post and your scripts would help a lot of peeps if you have the ability to share them… ;o). Maybe even a good article on SSC.com.

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 )

Connecting to %s