Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Archive Backups

By Steve Jones,

http://www.hyperorg.com/blogger/images/ironmountain_archive.jpgNot 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

Everyday Jones

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:

Total article views: 215 | Views in the last 30 days: 1
 
Related Articles
FORUM

Archive database

Archive database

FORUM

Data Archiving in replicated database

Data Archiving in replicated database

FORUM

Database Archiving Tool

Database Archiving Tool

FORUM

Reporting on archive database

Report data from active and archive database

ARTICLE

Archiving

It seems that archival isn't on the mind of most designers when they first build a database, which i...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones