SQL Server Script to Setup Change data capture

Below is the script to enable change data capture to customer table.

USE DB_name

GO

-- Enable CDC on the database.

if ( not exists (

SELECT name, database_id, is_cdc_enabled FROM sys.databases

WHERE is_cdc_enabled = 1 AND name = 'DB_Name' )

)

exec sys.sp_cdc_enable_db

GO

if (object_id ('cdc.CDC_Table_Name_CT') is not null)

begin

exec sys.sp_cdc_disable_table

@source_schema = N'dbo'

, @source_name = N'Customer'

, @capture_instance = N'CDC_Table_Name'

end

-- Enable CDC for a set of columns in the customer table.

exec sys.sp_cdc_enable_table

@source_schema = N'dbo'

, @source_name = N'Customer'

, @role_name = N'cdc_admin'

, @capture_instance = N'CDC_Table_Name'

, @supports_net_changes = 1

--Switch partition by default is 1. That's it ignores all the changes with table metadata and do not record changes if a partition is added or removed from the table.

--, @allow_partition_switch = 0

, @captured_column_list = N'

Col1,

Col2,

, @filegroup_name = N'FG1';

GO

--grant permission to the role

GRANT SELECT ON cdc.CDC_Table_Name_CT TO [rolename]

GO

GRANT DELETE ON cdc.CDC_Table_Name_CT TO [rolename]

GO