Restore CDC Data
-
Hi every body, I have a very special an uncommon! issue with CDC data migration, this is my problem: we had enabled CDC backup on our DB setting cleanup date for 2 years, so after 2 years we have old CDC data only in DB backups, now our customer needs reports base on CDC data in past 4 years, so the only way we can do that is reinserting old CDC data in corresponding tables by script, we replaced "change_tables" and "lsn_time_mapping" and set the data cleanup date for 10 years. every thing is OK until the cleanup job runs and removes the old data, is some data missed for restoring or is any setting avoid this cleanup? every suggestion would be appreciated thanks
-
Hi every body, I have a very special an uncommon! issue with CDC data migration, this is my problem: we had enabled CDC backup on our DB setting cleanup date for 2 years, so after 2 years we have old CDC data only in DB backups, now our customer needs reports base on CDC data in past 4 years, so the only way we can do that is reinserting old CDC data in corresponding tables by script, we replaced "change_tables" and "lsn_time_mapping" and set the data cleanup date for 10 years. every thing is OK until the cleanup job runs and removes the old data, is some data missed for restoring or is any setting avoid this cleanup? every suggestion would be appreciated thanks
I found the solution, may be useful to someone, I should update the start_lsn field in the change_tables to map to first entry in the CDC table. This table is queried by most of the system-defined procs/functions, hence this needs to be updated with the accurate start_lsn: UPDATE cdc.change_tables SET start_lsn = (SELECT MIN(__$start_lsn) FROM cdc.dbo_MyTable_CDC) WHERE capture_instance = 'dbo_MYTable';