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.

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:

• Run sp_Blitz on each of your servers and look for priority 1 warnings about databases not being backed up
• If you think you’re doing a good job, run sp_BlitzBackups to see your biggest RPO risk from the last couple of weeks (the moment in time when you would have lost the most data on each database)
• Get Ola Hallengren’s Maintenance Solution installed in at least one dev/test/staging environment and start looking at the jobs it creates – if you’re into PowerShell, the Install-DbaMaintenanceSolution cmdlet from DBAtools.io can help

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:

• Choosing Your RPO and RTO
• RPO, RTO, and Recovery Models
• Restoring from Oops Deletes

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