CDC Retention Period

With reference to my earlier article on restoring CDC, here is one more catch!

By default retention period is 3 days (4320 minutes). So when we restore CDC backup and then enable the job, the job tries to clean up all data from the restore which is older than 3 days. Hence we have to make sure the retention period is passed as a parameter while we re-enable the job.

Here is the piece of code which needs to be updated
EXECUTE sys.sp_cdc_add_job
@job_type = N'cleanup',
@retention = 43200; --for 30 days
GO

You can also change an exisitng CDC job using the below code
EXECUTE sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 21600; --for 15days
GO

This information is stored in MSDB.sys.cdc_jobs

Restore a CDC Enabled Database

You might not be able to restore a CDC Enabled DB from a backup using the GUI(Management Studio). Below is the script which explicitly mentions about KEEP_CDC. Once you restore the DB, enable the cleanup and capture job and the DB should be completely up with CDC enabled.

RESTORE DATABASE Sample
FROM DISK = N'E:\SQLBackup\Sample_07232009_1058.bak'
WITH FILE = 1,
MOVE N'Sample' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV\MSSQL\DATA\Sample.mdf',
MOVE N'Sample_log' TO N'E:\Program Files\Microsoft SQL Server\MSSQL10.SQLDEV\MSSQL\DATA\Sample_1.ldf',
NOUNLOAD,
STATS = 10,
KEEP_CDC
GO

and then

USE Sample
GO
EXEC sys.sp_cdc_add_job @job_type = N'cleanup'
GO
EXEC sys.sp_cdc_add_job @job_type = N'capture'
GO