backup and delete data from SQL 2008

Question

hi all,

this sounds simple buy I cant seem to get a good steer on it. I have a database and some of the child tables might have around 1 million records added to it in a week. After about 2 or 3 days this data is redundant and can be removed but I would like to keep it for archiving and maybe reporting in the future.

The system is inactive 1 day a week and I want to run a backup on that day.

I want to take all the records more than 1 week old, move them to another database with their PK,s etc and then delete the from the current DB.

I was looking to see could I use a cumaltive backup in SQL using publish subscribe or should I write a Store proc and call it as a job every week. I was looking at the merge command which seems handy. Also it seems to delete this amount of data can be slow so is there a fast delete if I take this approach.

looking for best practice and guidance... Mick

Answer 1

0

You ought to get a few answers along the lines of making a data warehouse out of this and using SSIS, both of which I am not involved with enough to make a good answer.

The one thing I would say is that to get rid of the data I would copy the data you want to keep into a new table, drop the old table and then rename the new table to the old table name. If there are lots of constraints that make this messy then I would change the drop table step to be a truncate table and the re-insert the data from the new table back in and drop the new table. truncate will work much more quickly than delete

answered Jun 07 '10 at 06:41 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

thanks Fr J. Indeed you are correct, I dont want a data warehouse. I simply want to move old records that are not currently needed to another SQL 2008 database, maybe on another server. I thought the publish/subscribe might work but I think when I delete records on the source after moving them, the next time I sync it will remove them also on the backup. Just wondering how people do this... regards mick

Jun 07 '10 at 07:45 AM Doiremik

In that case maybe you can use a Backup/Restore process to get the data to the new location and then execute my suggestion to remove the data on production? Or database snapshots?

Jun 07 '10 at 08:55 AM Fatherjack ♦♦

(comments are locked)

Answer 2

I've had similar issues in the past, while working in the Conveyancing business. What I did was create an "Archive" database with essentially the same schema but without the Identity constraints and Foreign Key relationships on the various table.ID fields, and put together a program that worked roughly as follows:

For each Case that's "archivable"
    Copy case data across to new DB
    Delete data from the existing DB

Can you say "Distributed Transaction"?

The "Archivable" criteria were generally along the lines of:

Has the work on the case been completed?
Has the case been marked as Closed?
Has the case been idle for a (configurable) time interval?

(This time interval was, initially, 2 years, then 1 year, then 6 months. I don't think we took it down to three months, but it was an option...)

I then put together a archived case view in case people wanted / needed to look at the old data, and also rewrote the application that built the Reporting database (a nightly task...) to grab data from both "Live" & "Archive", timing it to run at a time when the Archive program wasn't running.

Getting the data back from Archive to Live is left as an exercise for the reader. ;-)

Answer 3

0

I think the data can be also restored with the recovery tables sql 2000 program

answered Aug 17 '10 at 09:09 AM

daspeac
1

I am not sure that this utility does what the Mick wants, and even if it did it is targeted at 2000 and he is using 2008.

Aug 17 '10 at 03:55 PM TimothyAWiseman

(comments are locked)

backup and delete data from SQL 2008

Follow this question

Related Questions