We are trying to archive a huge table with ~170 million rows, which is partitioned, has change data capture (CDC) enabled and has ~5 foreign key constraints.
We decided on the archival since the daily full backup is taking a long time, and so we are planning to keep the archival table in different drive.
We tried the below ways, and all are taking really long:
We can’t go for inserting latest data to another table and then rename the tables accordingly because of the foreign key constraints.
SSIS package to insert to archive table old data is taking ~6 days in batch insert
BCP is also taking ~7- days.
Please suggest any best idea/suggestions considering CDC and foreign keys.