THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Best Practice: Backups

What if I told you to take your latest production backup, restore it on a different machine and try using the database? Are you comfortable with that task? Do you think it will work? When was the last time you tested your backups?

Do you even have a backup?

Why am I asking all these things? Because your data is as good as your last good backup.  Is your data backed up regularly? You will say “Of course it is we use [Insert expensive backup solution here] for all our enterprise backups”. Prove it, go to work on Monday and ask them to give you the latest backup. I bet out of a 100 people who ask this question to their backup team there will be several people without a backup file.

Here is another problem: three years ago the backups were taking about 1 hour. The backup started at 12 it would be done at 1, at 1:30 a job from another machine would ftp the file down. Two years later the backup takes 2 hours to complete, you didn’t realize this. Can you guess what will happen if you try to restore once of those backup that were moved by FTP? I will tell you it won’t work. What if there is no backup and you do a FTP? Oh yes the 0kb file will be created.

Where do you keep your backups?

Are you backups in the same building? If you would say yes then you have a big problem. Let me tell you a little story. I worked for a company in New York City between 2001 and 2005. This company had their office in WTC tower one. To be safe they kept their backups in WTC tower two. Well I don’t have to tell you what happened with the backup. If you do store your backup offsite (and why wouldn’t you?) make sure it is at least 100 miles away. If you don’t want to go that far from your current location then pick a location which is safe from floods, fires and not worthy to attack.


Where is your Source Code?

Do you backup your source code? Most people will say they keep it in Subversion or Visual Source Safe. But does that get backed up? What happens if your building goes up in flames? What we do is we have a full source code backup every day. In addition to that we also have differential backups every n revisions. We have jobs that create these backups and then FTP them to 3 different locations. If you have 20 developers and you lose 6 hours of work then you have lost 120 * $$ (you do the math). This is the best case scenarios. If the backup was in the building together with all the workstations then you got a lot bigger problem to deal with.

SQL developers are notorious for not using source control. They will tell you that the database backup is their source control. A source control system does not have to be expensive; we use Subversion (which is free and better than VSS). You can either use Tortoise or the plugin for Visual Studio to do your check ins.

Published Saturday, July 14, 2007 10:40 AM by Denis Gobo



Dustin said:

I very specifically do not backup my source control in any conventional way.  I use a distributed revision control system and just check out my trees in multiple places (including one that's automated and on another continent).  When your revision control is not centralized, you generally don't have to think about backing it up.

My database backups are encrypted on the fly and sent out to S3 (in addition to my local copy).  It's all incremental, so for about $2 or $3/mo, I get all the disk space I can take fairly reliably.  Amazon doesn't claim 100% reliability on S3, but I'd have to lose the master, local backup, and stuff on S3 before I suffered any loss.

You're right though, I haven't done a restore in a while.

July 14, 2007 1:53 PM

Denis Gobo said:

Interesting, how long would a 300GB DB restore take with S3?

Also S3 doesn't have a SLA

We further reserve the right to discontinue Amazon Web Services, any Services, or any portion or feature thereof for any reason and at any time in our sole discretion. Upon any termination or notice of any discontinuance, you must immediately stop your use of the applicable Service(s), and delete all Amazon Properties in your possession or control (including from your Application and your servers). Sections 3, 5, 8 - 12, any definitions that are necessary to give effect to the foregoing provisions, and any payment obligations will survive any termination of this Agreement and will continue to bind you and us in accordance with their terms.

Taken from

July 14, 2007 2:01 PM

Dustin said:

In the case of a catastrophic failure that took out my entire DB and the local copy of my backups, it could certainly take a while.  As it is, I can restore a single table for any given day pretty much ever (I rarely prune since it's quite space efficient).

The model is pretty solid, though, and S3 was an afterthought.  I used to send all of the dumps to a machine I had offsite in a similar way, but that machine went away.  I could easily have multiple targets for this model.

The important part, really, is the filesystem structure that allows this to work.  S3 was the answer to where, and it's appropriate for *my* databases.

I put a brief writeup in my URL.

July 14, 2007 3:55 PM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Privacy Statement