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.