CDC – Change Data Capture (How to enable on a DB)

Posted: 08/03/2015 in T-SQL
Tags: ,

Have you ever thought of tracking the values which deleted,updated or even inserted into a specific table. Till SQL 2008 there was a standardized method called creation of trigger to log these details. Here I am going to discuss about a new feature introduced in SQL 2008R2 to capture the data, the feature name says what does it do Change Data Capture.

In this blog I wish discuss about how to enable CDC on a database.

I have created a database named CDC_Test in my Server. To enable CDC to a particular table we should enable the CDC property to the table by running the below command.

-- ================================
-- Enable Database for CDC template 
-- ================================
Use CDC_Test
EXEC sys.sp_cdc_enable_db

After execution of the above command you could see few table starts with CDC in your database.

CDC_01h

Once You have seen this tables in your database you can make sure that the database is CDC enabled.

TO disable the same We have a different system procedure.

USE CDC_Test
GO
EXEC sys.sp_cdc_disable_db
GO

Once You have run the SP in your CDC enabled database, the CDC property of the database will be disabled and all the system tables related to CDC will be dropped from the DB.

CDC_02

Before enabling CDC on a table we should enabled it in DB level.

CDC_03

the above command will help you to identify weather the database is CDC enabled or not.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s