Restoring or Moving a database with CDC enabled

When you have CDC enabled on your database, and you want to move it on another instance or restore it somewhere, you need to take some extra steps to make sure CDC is transferred correctly. When asking myself the question how this works, I decided to do some testing.

Create test resources
In order to test this, we need to create some test resources. I’ve created a new database, added a table and enabled CDC:

CREATE DATABASE [CDC_DB]

 ON PRIMARY

(NAME = N'CDC_DB', FILENAME = N'C:\Databases\#Backup\CDC_DB.mdf')

 LOG ON

(NAME = N'CDC_DB_log', FILENAME = N'C:\Databases\#Backup\CDC_DB_log.ldf')

GO

USE CDC_DB

GO

CREATE TABLE dbo.Customer

  (CustomerID INT CONSTRAINT PK_Customer PRIMARY KEY IDENTITY(1,1),

   FirstName VARCHAR(50),

   LastName VARCHAR(50))

GO

EXEC sys.sp_cdc_enable_db

GO

EXEC sys.sp_cdc_enable_table

  @source_schema = 'dbo',

  @source_name = 'Customer',

  @supports_net_changes = 1,

  @role_name = NULL,

  @index_name = 'PK_Customer'

GO

INSERT INTO dbo.Customer

  (FirstName, LastName)

VALUES

  ('John', 'Doe')

GO







Now that we have the resources, we can take a backup of the database:




BACKUP DATABASE CDC_DB TO DISK = 'C:\Databases\#Backup\CDC_DB.bak'







Restoring the backup
To show you the default restore doesn’t work, let’s restore the backup next to the original database:







RESTORE DATABASE CDC_DB_RESTORE FROM DISK='C:\Databases\#Backup\CDC_DB.bak'

WITH

   MOVE 'CDC_DB' TO 'C:\Databases\#Backup\CDC_DB.mdf',

   MOVE 'CDC_DB_log' TO 'C:\Databases\#Backup\CDC_DB_log.ldf'




If we now try to select data from the change table of CDC, we get an error message:










But CDC was in the old database, so why wasn’t it restored? If you look for a solution, Books Online / MSDN doesn’t say anything about this. But luckily other community members blogged about this before (see this and this article). There is an option you can add to your restore:







USE master

GO

DROP DATABASE CDC_DB_RESTORE

GO

RESTORE DATABASE CDC_DB_RESTORE FROM DISK='C:\Temp\#BackupTest\CDC_DB.bak'

WITH

   MOVE 'CDC_DB' TO 'C:\Temp\#BackupTest\CDC_DB.mdf',

   MOVE 'CDC_DB_log' TO 'C:\Temp\#BackupTest\CDC_DB_log.ldf'

, KEEP_CDC

GO

If you add “KEEP_CDC” to your backup statement, it will also restore CDC. But that’s not your only option.

Detach and Attach

Another option you have is to detach your database, copy the files to another location, and attach the database again. Let’s detach the database first:

USE master

GO

ALTER DATABASE [CDC_DB_RESTORE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

EXEC master.dbo.sp_detach_db @dbname = N'CDC_DB_RESTORE'

GO







Now that’s done, we can copy the files to another directory if needed. You could also just attach it on the same instance:







USE master

GO

CREATE DATABASE [CDC_DB_RESTORE] ON

( FILENAME = N'C:\Temp\#BackupTest\CDC_DB.mdf' ),

( FILENAME = N'C:\Temp\#BackupTest\CDC_DB_log.ldf' )

 FOR ATTACH

GO




This also allows you to use CDC like nothing happened. The database is still CDC enabled, the change tables are there, and the captured data is still in the change table. But there’s one thing missing when you move your CDC database to another instance: the capture and cleanup jobs.

CDC Jobs
So how do we recover the jobs? Script them from one instance, and create them on the other instance? There’s a better way to do that. You can just run the statement below to add the jobs to your instance:

USE CDC_DB_RESTORE

GO

EXEC sys.sp_cdc_add_job 'capture'

GO

EXEC sys.sp_cdc_add_job 'cleanup'

GO

And if you move your database, you can also clean your instance, and run the statement below to remove the leftover jobs:

USE CDC_DB_RESTORE

GO

EXEC sys.sp_cdc_drop_job @job_type = N'capture'

GO

EXEC sys.sp_cdc_drop_job @job_type = N'cleanup'

GO




It is possible, but…
Moving or restoring a database with CDC gives you a few challenges, and even though there are some issues, it’s not impossible. But before you start, you need to think about the approach you need to take, in order to move/restore a database without losing CDC. Depending on your environment and possibilities, you might want to use a specific option, or perhaps can’t use the option you would like. There is a way around, but I would recommend you to test it first, before you run this on production databases!