Just why are you backing up your database?

  • Article

So, I'd like to make a couple of assertions about backup and disaster recovery that will hopefully get you to thinking, and just might change the way you think about the topic.

There are two distinct motivations for doing backups, and they imply differing requirements and solutions.

OK, what am I talking about?  Ask yourself the question, as obvious as it seems, "Why do you take backups every night?"  I'm sure you'll come up with lots of answers, but if you think about it, they fall into two classes:

  • Disaster Recovery
  • Archiving and records retention

I will argue that these are very different requirements.  For the first case, if you are doing a backup for DR purposes, you almost always want the very most recent version of the database that you can get.  1 ms before the disk crashed, or before the user hit COMMIT on that DROP TABLE where he thought he was in the test DB would be perfect.

I have rarely if ever seen a requirement to recover from a catastrophic outage from data that's over a week old.  So, this implies that you may not need to keep this class of backups around for long, but they must be very quickly available.

In the second case, you do need to keep the backups around for a long time.  In some cases, a VERY long time.  But, for archival purposes, they really don't need to be so fine-grained.  If you need the database from Q1 2005, you probably don't need to see it in 12 hour increments for every day in the quarter. Typically, your business cycles will dictate that there are distinct times from which you may need to see the data.

So, how can this be exploited?

If you separate the two backup requirements, you can come up with solutions for DR like disk snapshots or simply keeping the backups on spinning (disk) media for a week or so, where this would clearly not be economically feasible for archival purposes.

On the other hand, for archival purposes, you might be able to get away with far less frequent backups, something which would clearly not be acceptable for DR purposes.

You can see then, that you can combine these to come up with strategies which give you very fast (in some cases instantaneous) backups, very fast restores, and far less time spent in taking archival copies of your database.

Kevin Farlee

SQL Storage Engine PM