Backup, Dear Friend!
Letters of OpinionBackups10 Comments
In my line of work, which is databases, the ability to recover from a disaster is a key part of the job description. Things go wrong. Hardware fails, application code has bugs. Users or other applications can write terrible queries that update, insert, or delete the wrong records. The list of Things That Can Go Wrong is endless.
Bear with me because I’m going to talk about how enterprise backup strategies apply to regular folks.
Enterprise databases come out of the box with recoverability built in. But you still have to enable those features and then use them. Backup and recovery strategies look different depending on the needs and how large the database is. Since few individuals have the need for Enterprise level backup and recovery, I’m going to limit this discussion to simpler, but no less crucial strategies.
SQL Server has the ability to do what’s called a point-in-time restore. Assuming all the elements are in place, it is possible to restore a database to the exact condition it was in one second before someone updated the wrong records. (It’s a little more complicated, of course.) If the Point-in-Time restore features are not enabled (that is, you are not logging and backing up transactions), then you can only restore the database to the state of its last full backup. (Or full backup plus differential, if you’re doing diffs.)
So. What does your recovery plan look like if you’ve never backed up the database?
Answer: start looking for a new job
What happens if your last full backup was yesterday at 6:00AM and today at 9:00AM someone deletes all the records in your users table and you were only doing full backups?
Answer: You can restore your database to its state at 6:00 AM yesterday. You lose all the data that changed or was added between then and 9:00 AM.
What happens if you’re doing daily full backups and backing up the transaction log every 15 minutes?
Answer: Assuming you have access to the server, you can restore the database to its condition just prior to that terrible update.
What happens if you’re doing the required level of backups with your backup files going to the same server as your database and your database server dies?
Answer: Start looking for a new job, but also send a hard side-eye to the Network Admin.
What happens if you’re doing the required level of backups with your backup files also going another server, but your server room overheats and kills all your servers?
Answer: Consider helping your Network Admin find a new job. You might also need to be looking because you should have been insisting on off site storage.
What happens if you’re doing the required level of backups with your backup files also going another server and to an offsite location and you keep two days of backups locally and six months offsite and you need a copy of the database from seven months ago?
Answer: This is probably the CIO’s fault for trying to save money. Or, it’s in the Service Level Agreement (SLA) so all you can do is say, “Sorry. Talk to the boss.”
What happens if you’re doing daily full backups and backing up the transaction log every 15 minutes, and you do your restore and the restore fails because one or more of the backups are corrupt?
Answer: Start looking for a new job.
Do You See What I Did There?
It’s not enough to be backing up your database (or your computer). You have to test your ability to restore. You should do this often.
There’s another good reason to test your backups. If you need to restore it’s usually because something went wrong. It likely means you are stressed out. The more familiar you are with the process and the more often you have done restores, the less stressed you will be. You’ll also be able to restore faster because you will have all the required information, logins, and accounts at hand.
What’s in It For Me?
By now, if your eyes weren’t crossing from all that database talk, you might be freaking out about your ability to restore files to your computer, or to restore the whole computer. Good. These are things you should be thinking about.
Too often I hear of people who were working on an unsaved file for several hours and then zzzzzzzpt! Gone. Please, please, save your files right away. Get in the habit of hitting Control-S or Command-S.
Most Word Processors have an internal backup system such that it takes a backup of your open files every set period of time. (Kind of like a transaction log backup!)
Have you looked at those settings? Do you know where those backups are stored? How often are they recycled? If you’re on a laptop, you might want to increase the time between internal snapshots to reduce disk activity and save battery life – but understand the risk. For a desktop computer, you should consider decreasing the time between those snapshots. I set mine to every 10 minutes.
Word Processors also often have the ability to store undo and redo history. Have you looked at those settings? I always set those figures to far above the default.
More More More!
You’re backing up your computer, right? Where are you directing that backup? To locally attached storage? To storage in the cloud? You should be doing both. Cloud storage is not in your control. The service could shut down. Your credit card could have expired and you lose access… Your locally attached storage could fail or burn down with your house.
The Minimums
If you experience a failure of some sort, how fast can you get back to work? A full restore could take a long time. Make sure you are able to restore the files critical to your business and life. For authors, this might be the Work In Progress. For others, perhaps this is your folder of tax documents or family photos, scans of deeds, and the like.
If you use a backup service like Carbonite make sure you have your most critical files backed up elsewhere. Just in case. If you have local backup storage, make sure you have those crucial files stored in the cloud and in a place that isn’t your house.
Are You Sure?
How do you know you haven’t misconfigured your backup process? What if you’ve run out of space and all your backups are silently failing? Your backup files could be corrupt. Maybe you were using Dropbox when they discovered there were certain conditions when files were unexpectedly deleted.
TL;DR Review your backup strategy. And test your ability to restore. Right now.
So. What does your recovery plan look like if you’ve never backed up the database?
Answer: start looking for a new CAREER ;-)
Testify! Our hard drive crashed in late November last year…thank God I’m a freak about backing up our data. I have a huge digital music collection, not to mention the books, photos, and financial data I keep on the computer. And the damn thing just failed AGAIN last month. It makes me sick to think of how much we could have lost. I also minimized the time it took to get back up and running after I put together a checklist of what needed to be restored last time. Yes, weirdly compulsive, but also back up with minimal data loss.
Dear god, yes. Having that backup has saved me more than once. The time my hard drive bricked the day after Christmas (that was a fun trip to the Apple Store). The time my husband put a drink where I’d repeatedly asked him not in our shared work space — and proceeded to knock it over, with all the liquid hitting my laptop. (We now have separate desks.)
I used to just do that locally to my time capsule. Then we ended up in a move that so was horribly chaotic that I subscribed to a cloud service because I worried that something would happen to the time capsule; after all, everything else was going wrong. Now, all my data is backed up, including those on my external drives.
Blessed are the pessimists, for they hath made backups.
Amen! My laptop was stolen three weeks ago and it took two long phone calls to Apple support and a visit to the genius bar to figure out how to restore data from our Airport. And even then it’s possible my emails and address book are lost forever.
@Michelle Knight: LOL. Yes. You are right.
@Phoebe Cameron: You are DBA material.
@Caro: High five!
@Janet Mullany: Oh, ouch. I hope you’re able to recover the rest. And whew! That you had Airport. And so sorry about the stolen computer!
I just emailed my manuscript in progress to 2 separate email accounts b/c your piece reminded me to backup my current work. Thanks!
We have a home server we use as a NAS and an external drive for additional redundancy. That’s three points of backup, with a fourth in the form of dropbox for super important stuff like my uni assignments.
Need to get something automated, after the naming protocol is checked and duplicates removed.