When CDC is turned ON against a table and the underlying table schema changes,
such as adding/removing a column, changing datatype etc, these changes are not
captured in the CDC table. Below are the steps on how to capture these new changes.
In summary, we have to copy the data from the CDC table to a temp table and
after re-enabling CDC on the updated table, assign the correct __$start_lsn
in the cdc.change_tables
Step 1: Assumption is that CDC is turned ON against table OrigTable and changes are already being tracked in dbo_OrigTable_CT.
Step 2: Copy the existing change tracking into a temp table
SELECT * INTO cdc.dbo_OrigTable_Temp FROM cdc.dbo_OrigTable_CT
Step 3: Suppose a new column NewColumn is added to OrigTable (the assumption is that the new column has been added such that the data in the dbo_OrigTable_CT still exists). Care must be taken when updating a table schema such that the actual table is not dropped and re-created as this will cause all entries in the CDC table to be wiped away. If this is unavoidable, take a BACKUP of the CDC tables. As a safety precaution, it is ALWAYS advisable to take a BACKUP of all CDC tables before any deployment. Hence Step 2 is performed before updating the OrigTable schema
Step 4: Add the new column to the temp table so that we do not have to manually add all columns when we copy back
ALTER TABLE cdc.dbo_OrigTable_Temp ADD <NewColumn> <datatype>
Step 5: Disable CDC on the source table, this will drop the associated CDC table
Step 6: Turn ON CDC with the new column
EXEC sys.sp_cdc_enable_table @source_schema=‘dbo’, @source_name=‘OrigTable’, @role_name=NULL,@captured_column_list=‘[ID],[FirstName],<NewColumn>’
Step 7: Insert values from the temp table back into the new CDC Table
INSERT INTO cdc.dbo_OrigTable_CTSELECT * FROM cdc.dbo_OrigTable_Temp
Step 8: Update the start lsn 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 ELSE the queries will return results POST
updates i.e. all the old data will exist in the CDC table but will not be retrieved
as the start_lsn does not match.
SET start_lsn = (SELECT MIN(__$start_lsn) FROM cdc.dbo_OrigTable_Temp)
WHERE capture_instance = ‘dbo_OrigTable’;
Step 9: Drop the temp table
DROP TABLE cdc.dbo_OrigTable_Temp
Step 10: Validate if CDC is working properly. After some DML statements,
check to see if we are getting both old and new CDC records
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_OrigTable(sys.fn_cdc_get_min_lsn(‘dbo_OrigTable’), sys.fn_cdc_get_max_lsn(), ‘all update old’) ch
You can learn more about CDC at: