We are trying to archive our data for the first time in our database. The database is around 250 GB. Our approach is as follows. Create a copy of those tables without any index or constraints. And then dump the old data onto archive tables.And then remove the archive data in main tables.

For this either we can copy everything into archive tables,truncate main tables and then repopulate them. Or copy only necessary data to archive table and delete from main table. Considering the fact that main tables would have index and constraints on them which would make more sense? Also would it be better to disable all indexes before this operation. If so, should I disable clustered index also?

  • is this system OLTP or OLAP? Are the tables you will be archiving Partitioned? Are you Using Different Filegroups? If you are going to move the indexes will help your move go faster, because you can read your data faster, if you delete your data it will go faster without indexes. stackoverflow.com/questions/3453833/… – Stijn Wynants Oct 22 '15 at 9:14
  • @StijnWynants Its a OLTP system, We dont have partition in our systems and all data is stored in default file group. – f444ran Oct 22 '15 at 9:20
  • 1
    Much depends on the percentage of data being archived. If it is most of the existing data is being archived, it would be faster to create new tables with the data you want to keep. Then drop constraints and non-clustered index indexes on the old tables, rename the old objects to archive names, rename the new tables to the main table names, and create constraints and indexes on the new tables. Finally, delete newer data from the archive tables and create constraints and non-clustered indexes you need on the archive tables. – Dan Guzman Oct 22 '15 at 10:32
  • @DanGuzman It would be around 20-40% of table. Hence the new tables would be archive. Also when i disable the index during move, FK gets disabled. They would enabled when i rebuild my index right? – f444ran Oct 22 '15 at 11:23
  • No, rebuilding the index on a referenced constraint does not implicitly enable foreign key constraints. If you disable indexes and constraints rather than drop/create, you'll need to alter the foreign keys afterward so that they are enabled and trusted. – Dan Guzman Oct 22 '15 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.