January 9, 2006 at 12:09 pm
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
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
January 10, 2006 at 7:37 am
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
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.
January 11, 2006 at 7:05 am
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
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply