T-SQL Tuesday #85 – Restoring the Backups

This month’s T-SQL Tuesday’s topic is about backup and restores. Thanks to Kenneth Fisher (b|t) for hosting this month!

The day before the topic was announced, I was working with another database system and I just wanted to do a simple backup and restore so I could reset my test database back to the point before my mistake in the script I was working on screwed things up. And I couldn’t do it. This wasn’t covered in the introductory admin class I took for this database system and I was feeling too dumb to figure out how to do it after doing a Google search on the subject. Backups and restores were some of the first skills I learned as a SQL Server DBA so it just never made sense to me that it wasn’t for other systems. On the most basic level, it’s about protecting your data – why is it not the first thing you learn? Maybe that’s just me….

There are a lot of details that can go into creating a backup strategy. But I want to talk about restores. Why? Because you do backups so you can restore them. (If I could remember who I heard this line from, I would give them full credit. But it’s too great of a line not use because it so true.)

In particular, I wanted to think about the reasons why we restore.

Disaster recovery

If your databases goes down, you want to be able to go right back to where things were before the crash. A full discussion of disaster recovery and high availability and how to set up backups and restores for those systems is an entirely separate topic. I’ve only worked in situations where our business requirements meant having a good backup strategy in place and running was enough. Whatever your requirements are for disaster recovery will probably be the driving force for how you design your backup strategy. But you need to plan not only how you’re setting up the backups so you can recover but the details of how you would restore the backups in case of emergency.

Data recovery

You know that call you get from the client when someone accidentally did that thing in the application and screwed up some of the data for several tables that needs to be set back to what it was before they did that thing plus they did more stuff after that which needs to stay and they don’t get around to telling you until a week later? You may not be able to just restore the database because it’s only a subset of data on some tables or any number of other reasons. Being able to restore a backup to a separate database and perform your T-SQL magic to do the compares and manually update, insert, or even delete the lost set of data makes you a bit of a rock star to the client.

Upgrades

You got finally got that shiny new server with the disk space, processors, etc. or even just a new instance of the latest version of SQL Server and you need to bring over the databases from the old server or instance – in other words, a “side-by-side” upgrade. Sure, there are a lot of steps that you would need to do afterwards to make sure everything works (realigning users to logins, setting up related SQL Agent jobs, confirming the database compatibility level, etc.) but restoring a database is one way to do this.

Test environments

Most of the restores I do these days are for test environments. Sometimes, it’s even just to test an upgrade. But I will also do restores to get a better set of test data from another instance. When I’m writing a script to upgrade the schema for the latest release of our application, as my opening thoughts prove, I sometimes make mistakes. (A shocker, I know!) Sometimes the easiest way to fix those mistakes is to start from the beginning. But even when the script is correct, being able to restore the test database helps me get the database back to a point where I can test the full script cleanly.

Something else?

There may be a couple of other reasons why you do a restore beyond these. I once saw a system where the database was restored into a second database so they could compare the end of day data to the beginning of the day data to look for discrepancies. And I’m sure there are reasons out there that I haven’t thought of yet.

 

Whatever your reason for restoring is, it comes down to what you need for your environment to work will affect how you restore your database. In my personal experience, I’ve only worked with the conventional database backup plan strategies with one or two gotchas for restores along the way. (Service Broker, I’m looking at you!) But just the way that we work with databases today is so different from when I first started and there are so many options that affect how you need to do a restore. We now work on-prem and in the cloud, have multiple high availability options, have functionality restrictions and conditions, etc. So you will need to understand what you use in mind on both ends of your backup\restore strategy as you figure out the details of how you perform the backups and restores. While I think backup strategies get a lot of attention, I think understanding why you’re restoring is just as important so you can make sure you know what you need to restore and plan your strategies accordingly.

Happy Restoring!