SQL Server – Restoring Database with Change Data Capture (CDC)

sql-server-2008sql-server-2012

I want to perform a database restore on SQL Server 2012 and copy the database to a different server. CDC is enabled on the original database.

I have managed to copy the database along with the CDC data using:

Restore Database dB4_res 
from disk = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\Backup\dB1_backup.bak' 
with move 'dB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res.mdf',
move 'dB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res_log.ldf',
move 'dB1_CT.ndf' to 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLSERVER2012\MSSQL\DATA\dB4_res_CT.ndf' 
with keep_cdc

where CDC data is stored in a different file db1_CT.ndf

This restores the CDC data in the new database. However it does not create the capture and cleanup jobs, required for future CDC operations.

So I create the capture and cleanup jobs as:

exec sys.sp_cdc_add_job 'capture'
GO
exec sys.sp_cdc_add_job 'cleanup'
GO

Now this operation deletes all past CDC data from the Change Tracking tables.

Please can anyone suggest how to fix this issue.

Thanks

Best Answer

Ok I got the answer.

I used the following:

EXEC sys.sp_cdc_add_job
 @job_type = N'cleanup'
,@start_job = 0
,@retention = 52494800;

This adds the cleanup job but does not start it immediately i.e. start_job set to 0 does the magic. Retention can be set accordingly (100 years in my case as we do not want data to be deleted)

Related Question