SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Maintain Change Data Capture Configuration when restoring a database

I have been working with a several clients that are leveraging Change Data Capture (CDC) as a tool for incrementally loading their Data Warehouse.  In most cases the clients are using database restores from various environments to create test environments.  When databases with CDC enabled is restored to the same server with the same database name, CDC remains enabled and all related meta data is persisted.  However, when restoring the database to another server or to the same server, but with a new database name, CDC is disabled and all related metadata is deleted.  To retain the CDC configuration when restoring, simply use the KEEP_CDC option when restoring your database.  The following script shows an example of how to accomplish this:




The key item here is to specify KEEP_CDC as an option of the restore.  As always, if you have any questions concerning this post please comment here or send an email to pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc, SQL Server MVP, MCTS

Founder SQL Lunch

Visit www.BIDN.com, Bring Business Intelligence to your company.


Posted by Steve Jones on 4 October 2010

Great tip, and one that's needed. This is likely something that's easy to forget and not include in your DR or test/dev refresh documentation.

Leave a Comment

Please register or log in to leave a comment.