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

more ▼

asked Jun 07, 2010 at 06:15 AM in Default

Doiremik gravatar image

71 6 6 7

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Jun 07, 2010 at 06:41 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

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, 2010 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, 2010 at 08:55 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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. ;-)
more ▼

answered Aug 17, 2010 at 09:28 AM

ThomasRushton gravatar image

ThomasRushton ♦
34.2k 18 20 44

(comments are locked)
10|1200 characters needed characters left
I think the data can be also restored with the recovery tables sql 2000 program
more ▼

answered Aug 17, 2010 at 09:09 AM

daspeac gravatar image


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, 2010 at 03:55 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 07, 2010 at 06:15 AM

Seen: 2163 times

Last Updated: Jun 07, 2010 at 06:15 AM