gistfile1.txt

/*
What CDC doesn't cover:
 * Adding/Dropping columns
 * Changing data types

Here's how you handle that
*/

--Back up current data
SELECT 
    dpc.*
INTO #original_cdc_data
FROM cdc.dbo_Posts_CT AS dpc;
GO 

--Get the current start LSN
DECLARE 
    @start_lsn binary(10);
SELECT 
    @start_lsn = ct.start_lsn
FROM cdc.change_tables AS ct
WHERE ct.capture_instance = 'dbo_Posts';

PRINT @start_lsn;

--Disable CDC
EXEC sys.sp_cdc_disable_table
    @source_schema = 'dbo',
    @source_name = 'Posts',
    @capture_instance = 'dbo_Posts';

--Make your changes here

--Re-enable CDC
EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'Posts',
    @role_name = 'cdc';

--Insert back up data into the new table
--You'll have name columns since you're renaming a column
--If you were altering a datatype or something else, you'd need
INSERT 
    cdc.dbo_Posts_CT
SELECT 
    ocd.*
FROM #original_cdc_data AS ocd;

--Set the starting LSN to the value from above
UPDATE ct
   SET ct.start_lsn = @start_lsn
FROM cdc.change_tables AS ct
WHERE ct.capture_instance = 'dbo_Posts';