Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    india
    Posts
    67

    Unanswered: Archival and purging

    How to implement a optimal archival and purging in MSSQL SERVER
    databases

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    ???? That depends on what you mean by archival and purging. You need to find out what the business requirements are for all of your data first. After you have clearly defined that, you can map out the data that will fit that business requirement. You can only then create the optimal archive and purging processes, which will vary greatly by what those requirements are.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Feb 2004
    Location
    india
    Posts
    67
    Thanks for that, My requirement is quite simple.
    I am working for a popular bank and I have to retain data only of the past 3
    years and other data must be archived.

    any clue/url links to assist me

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Create views for all tables that need to be archived/purged. Don't forget to take into account all dependencies. The views should have a WHERE clause that would filter out anything that is less than 3 years old:

    ...WHERE <date_field> <= convert(char(10), getdate(), 101)

    Use those views to archive the data. You can use BCP if you want to keep that as text files and then zip them up, or you can have a separate database that you can populate with archived data, then back it up, and then empty it (truncate for example on all tables). Then you use the same set of views to perform your deletes from your production database.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •