Back Up Strategy

Posted on 2005-04-30
Last Modified: 2010-03-19
          Let me explain the scenario
1: I have developed a system for an org, the org wants to keep the records for the previous five years, records before these five years must be removed from database, but backup of this removed data should be available on CD or any media.
2: their another requirement is that in future they must have the way to access that removed data, just to query or see the details.

its easy to back up &  then remove,
now the problem for me is that how to restore it again on the current database, coz it will disturb the current data by over writing old data,
i dont wanna restore from old backup, then again from new backup.
if i use another database  for viewing these removed/backed up data it will be a great time consuming task,
what to do
I hope my problem is clear

Question by:jawad_ashraf
    LVL 34

    Expert Comment

    I would create an "Archive Table" and move the records there.  You would just need to remember and archive any of the "child" records as well.

    Another option, if you can keep the data online is to partition the table based upon a date value in your table.  Horizontal partitioning to be more exact.


    Author Comment

    thanks brett
    first option is clear but not the second one
    date based partition?
    horizonal partition?

    and one thing if i use the first technique , the archive tables will contain all the old records,
    that will use more disk space, and if i take different backups on CDs and then reload them at the time when required, it will save the space. can i use another duplicated Db to be restored
    from these CDs backups.when checked can be unload


    LVL 34

    Accepted Solution

    Yes, you can restore to a different database.  For storage being as cheap as it is, disk space seems to be a poor point for an archive--performance yes (which can be handled by partitioning), but storage no.....

    Here is a really quick overview of partitioning:

    SQL2005 will have partitioning "builtin" so it isn't as difficult to implement.


    Author Comment

    Ok I ll check it and tell u again

    Author Comment

    hello again
     if i user the strategy of restoring Backup over another Database, will it be possible to restore from a backup which is related to another Database


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    I recently came across an interesting Question In EE ( and was puzzled about how to achieve that using SSIS out of the box tasks, which was i…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now