Enabling CDC while Restoring Database.

Restore the Source Database to Destination Database with parameter KEEP_CDC.

Only “KEEP_CDC” needs to be included in “WITH” Option while restoring database.


The above process will only work if the Source Database is CDC enabled and CDC jobs are already existing on Destination Server.

If the CDC Jobs for destination database are not exiting then two jobs <Databasename>_Capture and <Databasename>_Change needs to be created manually and needs to be added to msdb cdc jobs using below mentioned procedure.

use msdb


exec sys.sp_cdc_add_job ‘change’


exec sys.sp_cdc_add_job ‘cleanup’


Once the above query is executed you will get message as jobs started successfully. After this the changes made to CDC Enabled table will be captured in CDC table.

If the CDC Jobs are already existing for destination database then restoring database with KEEP_CDC option is enough after which it will capture all the changed made to CDC Enabled tables to CDC tables as the Jobs are already existing and running

Technorati Tags: ,,


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s