If a database is CDC enabled you need to follow few more steps while migrating to other instance or on the same instance.
As a first step we will create a CDC enabled database and will setup change data capture for a table with the below script
create database TestDB -- Creates a sample database
use testdb
create table test1 (id int, name varchar(20))
select name, is_cdc_enabled from sys.databases where name='testdb' -- Verify whether CDC is enabled on the database or not
use TestDB
exec sys.sp_cdc_enable_db -- This command enables CDC on the database
select name, is_cdc_enabled from sys.databases where name='testdb' -- Verify CDC flag updated on the table or not
USE TestDB
exec sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'test1', @role_name = NULL -- Enable CDC at table level, after executing this command it creates two jobs in the SQL Server Agent (CDC Capture, CDC Cleanup)
To disable the CDC use the below commands
Use TestDB
go
exec sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name = N'test1', @capture_instance='dbo_test1'
GO
Script I - Restore CDC enabled database on the same instance
restore database testdb from disk = 'C:\temp\testdb_full.bak' with replace
As the database exists on the same instance so it doesn't require any additional parameters required to restore the database (Just replace parameter is sufficient).
Script II - Restore CDC enabled database to a different instance (with same name or different name)
1. Take full backup of the database
2. Use the below script to restore on the target server
RESTORE DATABASE TestDB FROM
DISK = N'F:\Testdb_full.bak' WITH FILE = 1,
MOVE N'TestDB' TO N'F:\Data1\TestDB_data.mdf',
MOVE N'TestDB_log' TO N'F:\Log\TestDB_log.ldf',
NOUNLOAD, REPLACE, STATS = 10, KEEP_CDC
GO
If you observe the above command it has a new parameter along with regular parameters called KEEP_CDC which helps to restore database with CDC values
3.We need to run the below commands to create the jobs on the target instance.
Use TestDB
exec sys.sp_cdc_add_job 'capture'
GO
exec sys.sp_cdc_add_job 'cleanup'
GO
