DBA - How to Restore Database On Which Change Data Capture (CDC) Is Enabled


We have enabled CDC on a Database in Production environment. We want to take the backup from Production and then restore to UAT instance. After restoring we noticed that the CDC tables are not available in Database. How can we restore Database with CDC objects to UAT instance.


When we restore a database from a CDC enabled database backup, it does not restore the CDC object ( tables, functions etc) automatically. Also if you try to look for some options for Restore with CDC object etc. in Database Restore Wizard, you would not find anything. If we restore the database on the same SQL Server instance with same name, CDC objects will be restored and it will start working fine as CDC jobs are available on the server already.

If you try to restore with different name or restore to different SQL Server instance, It will not restore CDC objects by default. In Restore Database statement we have to include Keep_CDC.  You can generate the entire Restore Script by using Restore Database Wizard and then add Keep_CDC at the end or write it by yourself.

Here is sample script that will restore the database with CDC objects.

WITH FILE = 1, MOVE N'TestDB' TO N'E:\Test.mdf', 
MOVE N'TestDB_log' TO N'E:\Test_1.ldf',keep_cdc,