Change Data Capture (CDC)

Change data capture provides information about DML changes on a table and a database.

Easy Steps to run TTthe ChangeDataCapture (CDC)

USE [master]

Go

Create Database Change_Data_Capture

Go

Use Change_Data_Capture

Go

EXEC sys.sp_cdc_enable_db

Go

Go

Create table Test

(

TestKey Int Identity(1,1) Primary Key NOT NULL,

TestName varchar(25)

)

              

Use Change_Data_Capture

Go

EXEC sp_cdc_enable_table  'dbo', 'Test', @role_name = NULL, @supports_net_changes =1

Go

----Job 'cdc.Change_Data_Capture_capture' started successfully.

--- Job 'cdc.Change_Data_Capture_cleanup' started successfully.

Use Change_Data_Capture

Go

Select name, is_tracked_by_cdc from sys.tables

Go

insert into Test

select 'R1'

union select 'R2'

union select 'R3'

union select 'R4'

Go

Select * from cdc.dbo_Test_CT

Go

Update Test

Set TestName = 'R1MODIFIED'

Where TestKey = 1

Go

Delete From Test

Where TestKey = 2

Go

Select * from cdc.dbo_Test_CT

Go

delete from Test

Go

Select * from cdc.dbo_Test_CT

Go

truncate table Test

-- Cannot truncate table 'Test1' because it is published for replication or enabled for Change Data Capture.

drop table Test

Go

/*

_$operations = 1 Means row was Deleted

_$operations = 2 Means row was Inserted

_$operations = 3 Means value of row before update

_$operations = 4 Means value of row after update

*/

Go

use master

Go

Drop Database Change_Data_Capture

Go

SQL Server Agent needs to be Started before you run this program.