Post reply

Unusual database backup requirement

  • January 9, 2006 at 12:09 pm

    #111199

    Hi,

    I have an unusual database backup requirement. I would like to know your opinion(s) in this regard.

    Task: Backup a partial table.

    There is table with approx. 2.5 M records. Once the "backup" is done, all those records in the table should be deleted.

    Should we need to restore the data, the "restore" process should put this information from "backup".

    Please let me know if this makes any sense.

    Thank you.

  • January 9, 2006 at 1:09 pm

    #613666

    Have a separate table.

    BEGIN TRANS

    INSERT INTO dbo.YourSeparateTable

    SELECT FROM dbo.YourOriginalTable

    WHERE [qualifying criteria]

    DELETE FROM dbo.YourOriginalTable

    WHERE [qualifying criteria]

    COMMIT TRANS

    Then BCP out dbo.YourSeparateTable

    Then TRUNCATE dbo.YourSeparateTable

    Incidentally a fast way of generating the structure of YourSeparateTable would be

    SELECT * INTO dbo.YourSeparateTable

    FROM dbo.YourOriginalTable

    WHERE 1=0

    LinkedIn Profile

  • January 10, 2006 at 7:37 am

    #613802

    The only other thing to look at is constraints. You may need to disable/enable when trying to restore the data to the table.

  • January 10, 2006 at 8:07 am

    #613819

    BCP is nice, if you can "identify" the records to be backed up, you can script this or even use a scheduled DTS job to send it out.

    Be sure your tape system is grabbing the files that are backed up.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • January 11, 2006 at 7:05 am

    #614050

    Thank you all for your suggestions.

    Steve, I was thinking about DTS option. How hard/easy it would be to write a DTS package and schedule it. Which would be advantageous between BCP & DTS?

    All the jobs that I have scheduled were through database maintenance plans.

  • January 11, 2006 at 11:57 am

    #614152

    bcp is very fast at extracting or importing data.

    As Data Transformation Services suggest they can do just about anything you want to data. DTS includes a specific task that is effectively bcp.

    Writing DTS to do what you want would be quite simple. If you used the Wizard to define the extract then editing the resulting package would be the best way to go. You would learn quite a bit about DTS that way.

    LinkedIn Profile

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply