This post explains how efficiently we can move a CDC enabled database to different environment.

To move a database from one environment to another environment, Firstly, we need to take back up of the database from the source environment.

BACKUP DATABASE CDCOrigin TO DISK = 'C:\Backup\CDCOrigin.BAK'

Once we move the backup file to the new environment, we have two options:

1. Restore database as always and configure/set up CDC freshly as in this post.

2. Restore database with KEEP_CDC option.

RESTORE DATABASE CDCCopy_CDC From DISK = 'C:\Backup\CDCOrigin.BAK'
WITH
	MOVE 'CDCOrigin' To 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\CDCCopy1.mdf',
	MOVE 'CDCOrigin_log' To 'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\CDCCopy1_log.ldf'
	--The below option would keep the CDC of original database
	, KEEP_CDC	

Once restore is completed successfully, we can quickly verify the configurations with below queries.

Select is_CDC_Enabled ,name from sys.databases where is_CDC_Enabled=1 
USE CDCCopy_CDC
Select name from sys.tables where is_tracked_by_cdc = 1

Wait! still there are few things to be done, configuring jobs for capture and cleanup process. KEEP_CDC option with RESTORE command will keep the CDC enabled and the tables tracked by default. However, we need to create capture and cleanup jobs manually as below.

USE CDCCopy_CDC
EXEC sys.sp_cdc_add_job @job_type = N'capture'; 
EXEC sys.sp_cdc_add_job @job_type = N'cleanup'; 

Caveats:

Do not enable this feature unless there is a good reason. The feature adds more space requirement to store the change data and increases disk activities. Be careful on configuring the capture and clean up jobs. If there are many databases with CDC ( due to non-multi tenant support), CDC on multiple databases will have a performance impact.