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.
