Backup, Dear Friend!
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.
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.