When restoring a database that uses Change Data Capture (CDC) in SQL Server, there are different scenarios to consider. In this article, we will explore these scenarios and discuss the solutions for each one.

Scenario 1: Restore the CDC enabled database on the same SQL instance by overwriting the existing database

In this scenario, when you restore a CDC enabled database on the same SQL instance by overwriting the existing database, CDC remains enabled and all related metadata is persisted. The Capture and Cleanup jobs are already present, so CDC will start working seamlessly.

Example:

RESTORE DATABASE sqldbpool FROM DISK = 'l:\sqldbpool.bak' WITH REPLACE

Scenario 2: Restore the CDC enabled database with a different name on the same SQL instance

In this scenario, when you restore a CDC enabled database with a different name on the same SQL instance, CDC will be disabled and all the related metadata will be deleted from the database. To preserve this information, you must use the “KEEP_CDC” option with the database restore statement.

Example:

RESTORE DATABASE sqldbpool_1 FROM DISK = 'l:\sqldbpool.bak' 
WITH MOVE 'SQLDBPool' TO 'L:\SQLDBPool1.mdf',
MOVE 'SQLDBPool_log' TO 'F:\SQLDBPool_log1.LDF', KEEP_CDC

Scenario 3: Restore the CDC enabled database on a different SQL instance

In this scenario, when you restore a CDC enabled database on a different SQL instance with the same database name, CDC will be disabled and all the related metadata will be deleted. To preserve CDC information, you need to use the “KEEP_CDC” option.

Example:

RESTORE DATABASE sqldbpool FROM DISK = 'l:\sqldbpool.bak' WITH KEEP_CDC

After restoring the database in scenario 2 and 3, you also need to add the Capture and Cleanup jobs using the following commands:

USE sqldbpool_1
EXEC sys.sp_cdc_add_job 'capture'
EXEC sys.sp_cdc_add_job 'cleanup'

By following these steps, you can successfully restore a CDC enabled database in different scenarios while preserving the CDC information.