DBA 101: Why Don’t People Run Backups

It happened multiple times this week. It happens multiple times every week. Some poor soul is posting on a message board, usually with the heading “URGENT” (why that one word so frequently, I just don’t know), that they deleted production data/dropped a production table/updated production data/dropped a database/received a data corruption error/whatever. Now, they need to get the data back. “URGENT, What do I do now?” And so you ask, as you should, what kind of backups do you have? Over and over the answer is: “Backups? What’s a backup” or “Oh, the system guys backup of the MDF files every night” or “We don’t really need those” or “We don’t have room to back up our databases” or some other excuse that simple comes down to, we didn’t set up backups and are now in a world of extreme pain.

Why? Why aren’t you running backups? I’ve thought about it and thought about it and I just can’t for the life of me understand why. I mean, there are lots of excuses, and the only one I find remotely acceptable is the “accidental DBA” excuse. “I just got handed these responsibilities and I’m finding out the hard way what that means.” Those people, I do feel sorry for. The rest of you… no excuse. Oh, you’re a developer and didn’t think that backups were necessary? Really? Do you put your code into a source control management system? Yes, of course you do because you’re a good developer. Why is that? Because you need a copy of the code in case something goes wrong…. wait for it…. Yes! That’s right. Same thing as a database backup. You say you’ve been working with databases for 3-5 years but you’ve never even heard of backups? But you’re reading this blog somehow and I’ll bet you read other blogs and you go to SQL Server Central or StackOverflow when you have questions (and you just posted an URGENT message). You see the phrase “backup” repeated in all these places over and over again, yet you don’t know what it is? Nope. Sorry, not buying it.

People, a lack of a solid full backup is absolutely a RGE (Resume Generating Event) waiting to happen. Stuff goes wrong. Remember the engineering corollary to Murphy’s Law, Murphy was an optimist (for the truly lost, Murphy’s Law). Most of us are still using disk drives that involve platters that spin at at some ungodly number of RPM with a rocker arm moving back and forth over these platters, want to talk about a location for physical failure. Electrical surges still get through surge suppressors at times and can modify memory in interesting ways. Sun spots and solar flares can emit high speed particles that could affect your drives. All that before we even start talking about having human being involved, and you know what a sad and dangerous lot they are. The fact is, you’re going to face breakages and data loss. You just are. So what do you do about it?

You run a backup. If you’re just getting started, I’d suggest getting a copy of “Beginning SQL Server 2008 Administration.” I wrote the chapter on backups and restores. I also wrote an article on 2005 backups, most of which is still applicable to 2008. In a nutshell, a backup is a copy of the data, structures, and log of the database that you’re backing up. It’s a bit-by-bit copy. It doesn’t run queries to read the data or manipulate it as it goes across. It simply copies what’s there. This is an important concept, because if you have a good backup of the data, you have a good backup of the data. There is no chance that a backup will subtly alter values. It’s either a good backup or it’s not.

But the thing is, if you don’t have a backup, run through SQL Server (or any of a number of third party products or even some of the more advanced transactionally aware SAN snapshot systems), then you don’t have a backup of the database. Simply backing up the MDF files won’t work because SQL Server maintains locks on these files, all the time. If SQL Server is running and the databases are attached, the files are locked. Running whatever system backup process you use will not work.

The thing is, if you’re responsible for the production databases, I don’t care what your title is or how long you’ve had that responsibility, then you’re the one that is responsible for ensuring that you have a means of recovering your system in the event of a failure or loss. Most companies I know are not going to be much interested in your excuses (even you accidental DBAs, sorry, but there it is). No, you might not be fired, although, you sure might be (remember, RGE). But absolutely and for sure you will have people standing around your desk waiting for you to pull a rabbit out of your hat (by which I mean recover the lost data) to be followed by multiple “what happened and how do we prevent it in the future” meetings (and lordy, do I hate those kinds of meetings, especially when I’m going into them with egg on my face instead of a halo over my head). You had better start setting up some method of backup for your databases. Oh, and while you’re at it, practice running your restores too. Backups are only half the store.