Restore the Source Database to Destination Database with parameter KEEP_CDC.
Only “KEEP_CDC” needs to be included in “WITH” Option while restoring database.
Ex: WITH KEEP_CDC
The above process will only work if the Source Database is CDC enabled and CDC jobs are already existing on Destination Server.
If the CDC Jobs for destination database are not exiting then two jobs <Databasename>_Capture and <Databasename>_Change needs to be created manually and needs to be added to msdb cdc jobs using below mentioned procedure.
exec sys.sp_cdc_add_job ‘change’
exec sys.sp_cdc_add_job ‘cleanup’
Once the above query is executed you will get message as jobs started successfully. After this the changes made to CDC Enabled table will be captured in CDC table.
If the CDC Jobs are already existing for destination database then restoring database with KEEP_CDC option is enough after which it will capture all the changed made to CDC Enabled tables to CDC tables as the Jobs are already existing and running