CDC Jobs missing after Database Restore
We use Change Data Capture (cdc) in our production environment on a few tables so we can do some asynchronous processing of data. Our developers have a pre-deployment environment which is a nightly restore of our nightly backups (this has an added bonus of testing our nightly backups). Usually the testers don’t care about testing the CDC processes, except when they are testing changes to the CDC asynchronous processes.
When we do the restore the database we do it with the Keep_CDC option. This makes everything look great, except the testers still don’t see any CDC data.
It turns out that everytime you drop a database with CDC enabled, SQL Server helpfully goes and drops the CDC capture and cleanup jobs. So every night our database is dropped and restored, our CDC is effectively disabled because the jobs are removed.
So how do you turn the jobs back on? Turns out Microsoft made a simple command in SQL to create the jobs:
USE [DatabaseName] GO EXEC sys.sp_cdc_add_job @job_type = N'capture';
That’s it. There’s plenty of other parameters you can use in that procedure, but that’s all we need for our test environment. We don’t use the built in cleanup job (we don’t want it to automatically clean up), but if you wanted the cleanup job you can change ‘capture’ to ‘cleanup’ and you’ll be up and running again.