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'
exec sys.sp_cdc_add_job 'cleanup'

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

Please can anyone suggest how to fix this issue.



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)

| improve this answer | |
  • Sorry, I didn't even look at the definition of the stored procedure when I posted my answer - seeing the parameters certainly would have made that more obvious. – Aaron Bertrand Sep 30 '14 at 16:52
  • No worries Aaron, I didn't look at the definition when I asked the question :) – gunnerz Oct 1 '14 at 14:35

I don't have hands-on with this but have observed someone else go through the same thing. To keep the old history you might want to migrate it to a separate table.

When the jobs get created, the default retention period is 72 hours. Unfortunately I don't know how to change that because the system procedures also run the job for you, before you have a chance to change the retention settings. What you might want to do is script the job from the old servers and create them yourself manually (instead of calling the system procedures), script them as disabled, and don't enable them until you change the retention setting to the right length.

| improve this answer | |
  • Thanks Aaron. I will try to create copies of original CT tables before adding the cleanup job. Do you know if there is any problem in not creating a cleanup job? I mean we have a requirement to never delete change tracking tables for audit reasons; so actually we do not need the cleanup job. – gunnerz Sep 29 '14 at 14:25
  • 1
    I don't know, I've never tried that. My colleague who went through this set the retention period to 100 years (max) and IIRC he never considered just not creating the cleanup jobs. Don't they do more than just delete old rows? – Aaron Bertrand Sep 29 '14 at 14:27

If you don't want cdc clean up to run, do not run the system stored procedure to enable it. Then the sql agent job won't get created.

exec sys.sp_cdc_add_job 'capture'

FYI - I have observed disabling CDC clean up long term may cause latency issues within your database. (I would assume you might have that issue too)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.