CDC Enable and Disable on SQL Server with File Group

--Cdc Enable

--Create table 
 

CREATE TABLE [dbo].[testScore](

[no] [int] NULL,

[score] [int] NULL




)
--insert values
insert into testScore

select 1, 2




--Enable CDc

--Enable
EXEC sys.sp_cdc_enable_db

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'testScore',

@role_name = NULL,

@capture_instance = N'dbo_testScore'



　
select* from cdc.dbo_testScore_CT




---Disable



　
exec sys.sp_cdc_disable_db

EXEC sys.sp_cdc_disable_table

@source_schema = N'dbo',

@source_name = N'testScore',

@capture_instance = N'dbo_testScore'




GO

declare @objid int

set @objid = (select object_id from cdc.change_tables where capture_instance = 'dbo_testScore')

delete from cdc.index_columns where object_id = @objid

delete from cdc.captured_columns where object_id = @objid

delete from cdc.change_tables where object_id = @objid



　
--Cdc with file group
ALTER DATABASE TEST1

ADD FILEGROUP CDC_1;



　
ALTER DATABASE TEST1

ADD FILE




(
NAME = test,

FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\Cdctest.ndf',

SIZE = 6MB,

MAXSIZE = 18MB,

FILEGROWTH = 1




)
TO FILEGROUP CDC_1;




--Enable
EXEC sys.sp_cdc_enable_db

EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name = N'testScore',

@role_name = NULL,

@capture_instance = N'dbo_testScore',

@filegroup_Name=N'CDC_1'