Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


archive strategy for large application database SQL 2008

archive strategy for large application database SQL 2008

archive strategy for large application database SQL 2008

I’m trying to design an archive strategy for a large SQL Server 2008 application database (that I didn’t create).

The database has two types of tables: static and run-specific. Static tables don’t change frequently but run-specific tables are completely refreshed once per month. Data from previous months is preserved using a Run ID naming convention: MyTableA_Run001, MyTableA_Run002, MyTableA_Run003 represents 3 monthly refreshes of MyTableA.

  • There are about 35 different run-specific tables in the database (like MyTableA), each one replicated for the number of months available.
  • The run-specific tables consume about 210 GB per month available.
  • There are about 230 different static tables in the database.
  • The static tables consume about 400 GB of space.
  • I am required to retain a minimum of 13 months of runs in the live application database at all times.
I am considering a plan to size the database server to hold all the static tables plus at least 16 months of runs and each time it reaches 16 months to archive the oldest 3 months of run-specific tables, taking it back to 13 months of runs. This archive would only need to run quarterly.

My biggest question is: in what format can I archive these tables so that I can
  1. Archive them in a compact format to store on a network share
  2. restore them into a copy of the full application database based on selected Run ID(s) when requested
Feedback and suggestions on an alternate plans are greatly appreciated.

FYI I am not a DBA but will be working with one to implement the solution. Options may be limited due to my environment.

-- BoulderRidge happy shades

RE: archive strategy for large application database SQL 2008

update: i just learned that the contents of the "static" tables may be updated as often as quarterly. So now it seems like the goal is to save a copy of all "static" tables once per quarter and all run-specific tables monthly.

same questions apply: what is the best format to preserve this data in a way that can be restored and accessed within a couple days when and if needed while keeping the archive volume reasonable?


-- BoulderRidge happy shades

RE: archive strategy for large application database SQL 2008

standard backup with compression on for the archives would work - split the backup into at least 4 files (try out which number suits better) - means faster backups and restores.
As for naming adding the run id to the filename would be one option, other would be to backup them up to a folder with run id.

As for the run specific tables - do they have compression on all of them? and for the static ones are they occupying all the pages fully or do they have "free space"?

If feasible (which might be) I would separate the static tables from the run specific ones and keep them on a different db - use synonyms so the app won't be affected. easier to backup the run specific more frequently than the static ones as these randomly change and backup is also bigger.


Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: archive strategy for large application database SQL 2008

Thank you Frederico!

When we are talking about archiving a minimum of 34 tables at a time, is it always easier/advisable to do so in some type of database backup as opposed to exporting the contents into flat files via an SSIS package? IF so why?


-- BoulderRidge happy shades

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close