1

I have a table in Microsoft SQL Server 2008 for which I have already enable CDC with 5 columns. It is already running and live in the production and having thousands of records. Now I need to add 4 new columns in the same table and enable data capturing for newly added columns as well.

I tried following scenario: 1. Created a new temp instance for CDC and copying all the data from original CDC to tempCDC instance. Now recreating the CDC instance with original name again and copying all the data from temp instance.

But in that case the start Lsn columns in the CDC.change_tables get reset. I stored the original start Lsn and and replace it with original start Lsn as well in the table CDC change_tables.

But still it is not working and it seems that when recreating the instance it is resetting the start Lsn on some places internally.

Thus I am unable to view the report based on CDC enabled table.

TT.
  • 15,774
  • 6
  • 47
  • 88
Banketeshvar Narayan
  • 3,799
  • 4
  • 38
  • 46
  • Possible duplicate of [Sql Server Change Data Capture: Preserving history when adding columns?](https://stackoverflow.com/questions/2654853/sql-server-change-data-capture-preserving-history-when-adding-columns) – Amira Bedhiafi Jun 14 '19 at 10:55

2 Answers2

0

The following query resolved my purpose.

DECLARE @startLsn binary(10)

SELECT @startLsn = MIN([__$start_lsn]) FROM [cdc].[dbo_City_CT]

UPDATE [cdc].[change_tables] set [start_lsn] =@startLsn
WHERE [capture_instance] ='dbo_City';

Where [cdc].[dbo_City_CT] is the final instance table recreated and having all the new columns included along with all the existing data.

So. This command has to run at last after "re-creating the instance and restoring the old data".

Banketeshvar Narayan
  • 3,799
  • 4
  • 38
  • 46
0

I have no SQL 2008 to test, but in newer versions you can do the following:

  1. Create a new capture instance for the table that is changed. You can have 2 instanced per table and the new one will include the new column(s).
  2. Add the content of the _CT table for the first instance to the _CT table of the second instance
  3. Remove the old capture instance for that table.

The way you tried is not possible, you should just use the new capture instance.

Adrian B
  • 41
  • 3