DBA Training Plan 2: Backups (And More Importantly, Restores)

When I first started out as a SQL Server DBA, I thought things were going well as long as the backup jobs ran successfully. I’d go into SQL Server Agent every now and then, make sure they were still running, and … that was the end of it. I figured if disaster ever struck, I’d just do a restore. How hard could it be? I was missing the entire point.

Nines don't matter if users aren't happy
I bought two of these as soon as I saw ’em. Seriously. Shout out to @mipsytipsy.

In theory, we design our backup strategy ahead of time with 5 Simple Questions About Your Backups, and we’ve memorized the 9 Letters that Get DBAs Fired: RPO, RTO, and CYA.

In practice, small disasters strike all the time when we’re not ready. The most common reasons to do restores aren’t to revive an entire server – it’s just to get back a few small tables or an individual database. Somebody ran the wrong DELETE statement or dropped a database in production instead of development, and next thing you know, we’re all scrambling. Let’s think through a few things ahead of time to make the crisis easier.

So since the only reason we do backups is so we can do restores, so I’m going to talk about the restore process first before I talk about backups.

Where to Do Restores

When you’re restoring code (stored procedures, views, triggers, etc) or individual tables, don’t restore onto the production server. I don’t like touching production servers more than I have to, and let’s face it – you’re already having a bad enough day as it is. That’s why you’re doing a restore, remember? So when you only need to pull a few objects or tables out, let’s do our work on a different server (like dev or QA) and leave production as it is. I’ve also written about restores in my ideal dev, test, and production environments.

After we’ve safely restored the right data onto another server, it’s easy to copy that data across to other servers. For simplicity and security, you can set up a linked server on the production box with read-only access over to the restore server. Then, from production, you can run INSERT statements using a SELECT sourced from the linked server tables.

However, if you’re restoring tables (not databases) over 10GB, you’ll probably want to do the restores directly on the production server to make the data copies faster. Just make sure you’re extremely careful with the scripting and the database names – we don’t want to restore over the top of your working production database.

This may require adding extra space to the production server. In one emergency, I freed up the necessary space by shrinking all of TempDB’s data and log files down to just 1MB. TempDB was on fast drives, perfect for a one-time emergency restore, and that particular server didn’t have any other activity happening due to the outage. We’re not always so lucky, but it helps to think out of the box like that.

A word of warning: if referential integrity is involved, like if you’re trying to restore tables that have relationships to other tables that you’re NOT restoring, then you can be in for a world of hurt here. We’re not going to cover that scenario – it really is different in each case.

Doing the Restore

Big picture, your restore process goes like this:

  1. Restore the most recent good full backup
  2. Restore the most recent good differential backup
    (you don’t have to restore all of the differentials)
  3. Restore the transaction log backups since that differential, in order
  4. Run RESTORE WITH RECOVERY to bring the database online

As you go through steps 1-3, make sure you use the WITH NORECOVERY option as you do restores. This leaves the database in a restoring state so that you can continue to apply additional backups to it. If you forget those two key words or accidentally use WITH RECOVERY, your restore is done at that point – you can’t apply additional backups. If you get that wrong, your restore has to start over again from scratch, so please, for the love of all that’s holy, double-check that option before you start the restore.

Doing all of this with the GUI sucks. The more backups you have, the longer this takes, and the more likely you are to run into errors. Instead, what you need is a script that looks at all of the backups in a folder, plucks out the most recent relevant files, and restores them for you automatically, in order, and that’s where sp_DatabaseRestore comes in.

Designing Backups for Faster Restores

sp_DatabaseRestore requires the database backups to be laid out a particular way in advance, though. It’s designed to work best with Ola Hallengren’s free maintenance scripts, which can handle backups, checking for corruption, doing index maintenance, and more.

In the past, you’ve probably used the built-in maintenance plans for backups. Those aren’t a bad way to start – I mean, compared to the alternative of not having backups at all – but now that you’re going through a full blown DBA training plan, it’s time to graduate to something more powerful. Ola’s scripts are much more flexible and capable than maintenance plans, even integrating with 3rd party backup apps your sysadmin team might be using.

Your homework for this episode:

In the next episode, we’ll think about the implications that these things have on the way we do our backups – how often we back up, how we approach failovers, how we do disaster recovery for the cloud, and more.

And let’s pause here for just a moment to notice: isn’t it cool that all of the stuff above is open source? It’s all totally free, built by other members of the database community just like you. During the course of this training plan, we’re going to be referring to a lot of community & open source stuff. As you work with it, think about joining the communities for each of those tools to meet other folks like you and start expanding your career horizons.

If you’re a member of our Recorded Class Season Pass and you want to watch the training videos on this stuff, here are your next steps for learning:

In the next post, we’ll talk about why you probably won’t want to restore the master database.

Previous Post
DBA Training Plan 1: Build a Server Inventory
Next Post
DBA Training Plan 3: Treat Your Servers Like Cattle, Not Like Pets

6 Comments. Leave new

  • This is a _great_ summary of SQL backup and restore. Following your advice on backups and restores has made a giant difference for us: We used to be afraid of touching our SQL Server — it was this scary thing with dark, unknowable risks. After we integrated your ideas and processes we feel so much safer around the SQL Server and more proud of the work we do. Thanks a million times!

  • Im so glad i stumbled upon the first article, and now this one. These are very helpful advices.
    Thank you!

  • demml julian
    July 19, 2019 3:57 am

    Hello, im very happy about your website and very grateful. Because we have no many dbas in my company who can help me with problems. With your website and community, everyday life is much easier to master

  • Brent, you’re advise is to do restores to dev/q&a servers. From the licensing standpoint, is it legal to restore production data to a Developer Edition instance? And link it to a production instance?

    October 5, 2023 7:53 am

    I would like to know more


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.