Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Archiving Data Expand / Collapse
Posted Wednesday, August 19, 2009 6:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 3, 2010 1:44 AM
Points: 5, Visits: 19

I am trying to achieve the same thing as you. I need to archive data from a Production database into an archive Database. The db still needs to be online. I need to purge the production database regularly so that i can keep the db clean and achieve maximum performance.

I need to also bear in mind that some of our customers use Sql Express which does not support replication, scheduled tasks etc. I tried Replication, but after cleaning up the production database, it filter the deletes to the archive database. I considered partitioning but you need Sql Enterprise for that.

I am moving towards a parameterized stored procedure. I need to get the rollback and the necessary checks in place to avoid duplication and i need to ensure the data integrity.

How do you go about archiving your data and would you have any other ideas?
Post #773435
Posted Wednesday, August 19, 2009 8:08 AM



Group: Administrators
Last Login: Yesterday @ 9:28 PM
Points: 34,372, Visits: 18,590
If you have good primary keys, that should prevent duplication. Make sure the archive database has the same PKs.

The process I'd use it to move a slice of the data from a table to a staging table, or an archive table with a date/time marker. Then join back to the original table to delete those rows. Wrap that in a transaction and do a slice of data at a time. Maybe 1000 rows, maybe 10,000, depends on hardware and load.

Set this up for all tables you need to archive, use dates somehow to determine what data to move, schedule this as a job.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #773545
Posted Wednesday, August 19, 2009 8:44 AM



Group: General Forum Members
Last Login: Thursday, December 17, 2015 11:50 AM
Points: 6,050, Visits: 5,314
Is the archive on the same server? If so, Steve has the right approach. If not it gets more complex.

One note: if the schema changes you have to remember to change the process and the schema in the archive, this often gets forgotten, right up until the moment that the process starts failing..

Post #773587
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse