Prev: Primary database deleted and need to stop log shipping now
Next: In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB** does not work for me on 2008
From: Richard Collette on 16 Apr 2010 13:20
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.

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.

How can the capture instance history be preserved, using the same capture
instance name, if at all?

Thanks,
Rich

/*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')


 | 
Pages: 1
Prev: Primary database deleted and need to stop log shipping now
Next: In SQL Server 2005 you can use OPENROWSET with the SINGLE_BLOB** does not work for me on 2008