Hello All, SQL instance - SQL server 2005 I have some tables that get created every day, 1 for each day transactions. I have many such tables(atleast 500-1000). I need to archive say tables older than 3 months. When the tables are archived (i.e. copied from main instance to archive instance, I need the original tables to be deleted from the primary DB. questions: 1. I am assuming T-sql scripts is the best way to go, am I right? or do we need to use SSIS? 2. is there any specific logic / method that I may need to use? any help is highly appreciated.
If I were setting this up, I think I'd use SSIS instead of straight T-SQL. Because you're moving the data between servers, straight T-SQL requires linked servers which can be somewhat problematic. Also, setting up retry logic or any other specialized logic within the processing will be more of a pain than it would be within SSIS where you have a lot more control. This means you can pull the data and then drop the table rather than running delete statements which might lead to large transactions within the system. It should be considerably faster too.
This can be done with triggers: 1. Create a Archive table for each table created 2. Create a After delete trigger on each table that inserts the data into a archive table before deleting 3. Create a proc working with a date parameter that deletes records from those tables, and then drops the original tables.