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 DataBaseNameGO
declare @rc int
exec @rc = sys.sp_cdc_enable_db
select @rc
|
USE DataBaseNameGO
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.
