When deciding on a backup strategy for a database, there are various things we must consider:

• Does this database need point-in-time recovery?
• What are the RPO (data loss) and RTO (downtime) goals? No one likes losing data or encountering unplanned downtime, but the business must decide on these goals so that we can setup an environment that can meet those goals. You want no data loss and no downtime? Okay, who is going to write the check for this?
• How much backup retention is needed short term and long term?
• How fast are the backups and how quickly can I restore?
• Should I use a SAN snapshot or similar if my database is a VLDB?

Sometimes we overlook things

Given my backup strategy, can I hit the RTO goal when needing to do a restore?

What if I need to restore lots and lots of transaction log backups?

There’s a script for that

I’ve got various scripts in my toolbox, one such script reads a folder and writes out the RESTORE LOG commands. This is handy when I need to setup an Availability Group, Database Mirroring or Log Shipping and need to get the secondary server caught up with the transaction log chain. It can also be used for an unplanned restore. The script assumes that the files are in order when sorted alphabetically.

I recently came across transaction log backups that were named in such a way that my script didn’t work. This system had Sun, Mon, Tue, Wed, Thu, Fri or Sat in the backup file names, so Monday files were listed before Sunday files. I was fumbling to fix the script, but then I just decided to manually work around it so that we could make progress on the task at hand.

While I was trying to make the script work, I thought to myself, “There’s no way I could hit the RTO goal here if this were an unplanned restore.”

Be prepared for an unplanned restore

Given your environment, you need to be ready to do a restore that can meet the RTO goal.

This must be practiced. If you can’t achieve the business’s RTO goal, your backup strategy needs to be changed.

Brent says: Like Mike Tyson said, everybody has a plan until they get punched in the cluster.