Sunday, October 3, 2010

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:

RESTORE DATABASE TestCDC

FROM DISK = 'C:\MMSQL\BACKUPS\TestCDC.bak'

WITH KEEP_CDC

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.

No comments:

Post a Comment