question

Doiremik avatar image
Doiremik asked

backup and delete data from SQL 2008

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

sqlbackup
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
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?
0 Likes 0 ·
daspeac avatar image
daspeac answered
I think the data can be also restored with the recovery tables sql 2000 program
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
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. ;-)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.