I was recently just letting my mind wander. As it’s prone to do, all sorts of things just entered into it. Things like:

  • It’s a wonderful time of the year.
  • Lot’s of places I’m going to with #sqllearning potential.
  • There’s a lot of resolutions I need to work on.
  • Considering how well I did with last year’s resolutions, it’s a fresh start to those.
  • Perhaps if I had more time, I could tackle some of those resolutions.
  • Maybe if I can automate some of those things that I do, I would have that time.

Hmm. Automation? Interesting. Because Hemanth.D (blog|twitter) is hosting the T-SQL Tuesday blogging party this month, and he wants all of us to post about Automation. Specifically:

You could write about, what options you would consider when automating something? Where do you draw the line? What are our preferred tools for automation? T-SQL, PowerShell, VBScript or Batch files(?) or maybe just share something that you automated in the last couple of years.

Okay, I can do this. So, here goes:

Earlier this year, I was reading SQL Server Microsoft Certified Master (MCM) Robert Davis’ (blog|twitter) blog series on Disaster Recovery. On day 3, he has a nice script for seeing what backup files you need to restore, and the order to restore them, for a database. It starts from the most recent full backup, then the most recent differential backup (if available), and finally all the transaction log backups since the latter of those two.

One thing that I’ve been planning on writing is a script to detect if the files necessary to recover a database are available. With Robert’s script, most of this work is done. However, I wanted this to run for all databases (not just a specified one as Robert’s does). I also want to check for the presence of these backup files.

So, I bring to you my modified version of Robert’s script. The changes to it are:

  1. All databases are checked, not just a single specified database.
  2. Robert’s script stops if a broken transaction log chain is detected. My modification has all backups, but indicates a broken transaction log chain at the start of the chain.
  3. The modified script checks for the existence of all of the backups files. (If one doesn’t exist, you have a broken transaction log chain (or worse))
  4. Various performance improvements.

What this modified script doesn’t do is check to see if multiple backup files written to the same file are all present in the file (though I’ll probably make this modification some day – sounds like a resolution?).

I’ve uploaded this script to my Code Library. I hope that you can find some use for it.

After you have verified the presence of your backup files, it’s time to start testing the backups. See Jason Brimhall’s post for how to do that!

Thanks for the topic!