Using KEEP_CDC option to keep CDC metadata

Having a database with Change Data Capture (CDC) enabled, we will need to take this recommendation into consideration during the restore process. There is one option to be used as part of RESTORE syntax. This is KEEP_CDC option which allows us to restore the CDC metadata as well when the database is restored to another SQL Server instance (or restore it in the same instance with a different a name, indeed). So, here is the example:

RESTORE DATABASE [TESTDB2] FROM  DISK = N'D:SQLBackupTESTDB.bak' WITH  FILE = 1, KEEP_CDC

While verifying that CDC metadata was restored as well, we will see that not only the database and tables keep CDC option enabled, but also all data inside captured by CDC is still there.

-- Checking if CDC is enabled for database TESTDB2.

select is_cdc_enabled,name from sys.databases where name='TESTDB2'

-- Checking if CDC is enabled for table MyTable.

select is_replicated, is_tracked_by_cdc, * from sys.tables

select * from [cdc].[change_tables]  

-- checking the data tracked for table MyTable.

select * from cdc.dbo_MyTable_CT 

This is not all. Now we must create the CDC jobs by executing:

USE TESTDB2

EXEC sys.sp_cdc_add_job 'capture'

EXEC sys.sp_cdc_add_job 'cleanup' 

Finally, you also can verify the jobs were created for CDC

USE TESTDB2

EXEC [sys].[sp_cdc_help_jobs] 

Having done that, the restore process of database (with CDC included) has been completed successfully. Thanks for reading!