I Have Trust-Issues with my SQL Server Backups

Early in my career, I worked in systems support. Part of my responsibilities included ensuring server backups were running properly. However, the organization had not formalized a process around actually testing those backups. I was new to the world of IT and I didn’t think anything about testing backups. “Ignorance is bliss”, as they say. Until it isn’t.

 As luck would have it, we were troubleshooting an issue where we concluded that we needed to restore from a backup. Cue the mad scramble to retrieve the entire collection of tapes necessary to ensure a full-recovery and then start the rather lengthy process of the restore operation. I think it only took us about 8 hours to realize the backup was worthless. I’ll spare you the details of the aftermath of our experiment of “testing backups in Production.” Use your imagination (or relish the memories of similar experiences) and let’s agree that it wasn’t pretty.

While my story applied to a file server incident, the same scenarios can occur with databases. The technologies involved change over time, but the principles associated with the problem do not change. We absolutely cannot assume that a backup is useful simply because the backup operation succeeded. We MUST employ a process of testing our database backups by attempting to run restore operations from the backup sets.

The challenge is that managing the process of testing backups adds to the volume of work DBAs must perform. I’ve never had a DBA tell me “Ben, I have a lot of time on my hands and I’m looking for more projects to complete.” What if we could save some time by automating the process of testing the quality of our backups?

Litespeed for SQL Server provides a feature that we call “Automated Restore.” One of the benefits of this feature is to address the problems of backup testing. Another benefit includes scheduling restore operations to update such systems as testing and development environments. This ensures your teams have realistic data for their efforts. You can imagine other scenarios where this kind of automation is beneficial. Let’s focus specifically on backup testing.

The “Automated Restore” process, like many things in Litespeed, is driven graphically by a wizard. Admittedly, we have an API that we publish so you could write your own code to perform the operations, but let’s keep things simple. Use the Home screen and click the Automated Restore button:

Choose the Automated Restore option:

And continue through the wizard. Now, I’m not going to provide screenshots of every single option available, but I will call out a few things:

  • Restore multiple databases, to any target, and choose from Full, Full and differential, or Full, differential, and t-logs:

  • Perform a wide variety of integrity checks after restore:

  • Automatically drop the database(s) after restore:

  • Create a regular or custom schedule to further automate the process:

  • Save time by using the script generated by the wizard and add it into your scripted processes (if you like that kind of stuff):

As you can clearly see, testing backups is just one of the possible scenarios available when using Litespeed’s “Automated Restore” feature. For those of you using Amazon S3 or Azure Blob Storage technologies, for your SQL Server backups, Litespeed supports backing up to and restoring from those cloud technologies.

I’ve focused on a very small subset of Litespeed’s capabilities. If you’d like more information, I would encourage you to visit Litespeed for SQL Server . Please know that if you’re interested in trialing the solution, there is a link to download on the aforementioned product page.