Not many of us archive data from our databases. That's understandable as I've heard that something like 80-90% of databases are smaller than 2GB, so space typically isn't an issue. But there are cases where space matters, with larger systems,  especially where there is a lot of read-only data.

I had a conversation recently with someone that had a number of images in their database and had decided to move the to an archive database. Their plan was to move all this read-only data there, but they didn't want to run a nightly backup of that data, since it rarely changes and it was large enough to impact the cost of storing all those backups on tape. This person's plan was to run a backup of the archive once a quarter and save it on tape.

Not a bad plan, but there's a risk element to that. It's easy to say that this data doesn't change and doesn't need to be backed up often, but is that enough? I'm not sure. If some disaster happens, will the backup of the archive be available? Will you be able to find that 2 month old tape and is it readable? Or is that backup file still on disk? Can you match up the most recent archive backup with the most recent database backup?

Possibly, and probably in many cases, but in terms of DR, I like to be sure. I think that I'd probably make more frequent backups, and perhaps add a table to my live database that stores information about which archive backup was last made. Something that could be updated from the archive backup job.

As data sizes grow larger and larger, the need for archiving will grow. Much of that archive data will be read only, and to reduce stress, you'll want to back this data up less. That's fine, but be sure you understand the risks, and you are aware of what it will take to recover this data in a crisis.

Steve Jones

The Voice of the DBA Podcasts

• Windows Media Podcast - 28.5MB WMV

  

• iPod Video Podcast - 19.7MB MP4

  

• MP3 Audio Podcast - 4.5MB MP3

  

The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there. Overall RSS Feed:

or now on iTunes!

Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

You can also follow Steve Jones on Twitter: