title: Sql Server Change Data Capture: Preserving history when adding columns?
body: <p>When a new column is added to table that is configured for change data capture (cdc), the capture instance table will not have the new column until cdc is disabled and re-enabled for the source table.  In the process the existing capture instance is dropped.</p>
  
  <p>I thought I could copy existing data out to a temp table and then copy back using the following SQL.  However, other CDC meta information, such as the cdc.change_tables.start_lsn, becomes invalid.</p>
  
  <p>How can the capture instance history be preserved, using the same capture instance name, if at all?</p>
  
  <p>Thanks,
  Rich</p>
  
  <pre><code>/*Change Data Capture Test - Alter table definition test */
  
  /*Enter restricted mode so we don't lose data changes during this process*/
  alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC OFF
  alter database ChangeDataCaptureTest set RESTRICTED_USER with ROLLBACK IMMEDIATE
  go
  
  /*Add a column to the table*/
  alter table dbo.Table1 add value3 varchar(20) DEFAULT '' not null
  
  /*Copy the existing change tracking into a temp table*/
  select * into cdc.dbo_Table1_temp from cdc.dbo_Table1_CT
  
  /*Add the new column to the temp table so that we don't have to map
  all columns when we copy back, note that we use NULL as the default*/
  alter table cdc.dbo_Table1_temp add value3 varchar(20) DEFAULT NULL
  
  /*Disable CDC on the source table, this will drop the associated cdc table*/
  exec sys.sp_cdc_disable_table 
  @source_schema='dbo',
  @source_name='Table1', 
  @capture_instance='dbo_Table1'
  
  /*Enable CDC for the table which recreates the CDC table*/
  EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'Table1',
  @role_name     = NULL,
  @supports_net_changes = 1,
  @filegroup_name = N'ChangeDataCapture'
  GO
  
  /*Insert values from the temp table back into the new CDC Table*/
  Insert into cdc.dbo_Table1_CT 
  SELECT * 
  From cdc.dbo_Table1_temp
  go
  
  /*Drop the temp table*/
  drop table cdc.dbo_Table1_temp
  
  /*Go back into multi-user mode*/
  alter database ChangeDataCaptureTest set AUTO_UPDATE_STATISTICS_ASYNC ON
  alter database ChangeDataCaptureTest set MULTI_USER
  go
  
  /*Add a new row to the table*/
  insert into table1
  values(12,'zz','g')
  </code></pre>