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:
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
Having done that, the restore process of database (with CDC included) has been completed successfully. Thanks for reading!