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
declare @rc int
exec @rc = sys.sp_cdc_enable_db
@source_schema = 'dbo',
@source_name = 'TableName' ,
@role_name = NULL
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.
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.