Create Change Data Capture ( CDC ) in SQL Server

Hi,

   Change Data Capture records insert, update, and delete activity that is applied to a SQL Server Databases and Tables. Change data capture must be explicitly enabled for the database manually, by default it is disabled.  After creating the CDC for table, Change Table (_CT) is automatically created under System table section as _CT appending to the original table name. This table contains all the modified, pre modified, newly inserted, deleted records with their details. This works from SQL Server2008 and works with Admin and should have SQL Server Agent.

Enable CDC at DB Level	Enable CDC at Table Level
USE DataBaseName GO declare @rc int exec @rc = sys.sp_cdc_enable_db select @rc	USE DataBaseName GO EXEC sys.sp_cdc_enable_table     @source_schema = 'dbo',     @source_name = 'TableName' ,     @role_name = NULL GO

Check whether Change Data Capture already enabled or not  and How to disable Change Data Capture in SQLServer

After enableing CDC you can find the additional table as dbo.TableName_CT . you can find the table in Tables -> System tables in your database.

Lets See what the newly created table has.

• __$start_lsn
• __$end_lsn
• __$seqval
• __$operation
• __$update_mask

The column  _$operation contains value which corresponds to DML Operations. Following is quick list of value and its corresponding meaning.

• Delete Statement = 1
• Insert Statement = 2
• Value before Update Statement = 3
• Value after Update Statement = 4 

The column _$update_mask shows, via a bitmap,   which columns were updated in the DML operation that was specified by _$operation.  If this was  a DELETE or INSERT operation,   all columns are updated and so the mask contains value which has all 1’s in it. This mask is contains value which is formed with Bit values

Now you can make changes in your original table by Inserting, updating and deleting and find the changes in backup table  as cdc.dbo_tableName_CT

View all the modified records by :   select * from cdc.dbo_YourTableName_CT

You can notice the system generated colums in the retived data.

_$update_mask shows via a bitmap, which columns are updated in DML operation.

In _$operation column 2 Shows for Insertion, 3 Shows data before updation of record, 4 Shows data after updation of record.