Programmer's Edge

Change Data Capture: Restoring A Database

Arie D. Jones -

Change Data Capture (CDC) is an awesome feature available in Enterprise Edition of SQL Server 2008. It enables tracking of changes all the way down to the column level and is a really great time saving feature if you are building complex data models that have the need for this type of thing..such as required by some regulation FISMA, HIPPA, DITSCAP, etc…

However, what happens when everything goes down hill and you need to restore from a backup…What then?

Fortunately, Microsoft has thought of a lot of different angles on this one. So if you are restoring a CDC enabled database you just need to keep in mind the following points.

  • If you are restoring to the same instance with the same name….NO PROBLEM. All your CDC settings and associated data is intact. Since the setting is per database and your CDC meta data and associated tables are kept within system tables of the database you are safe.
  • If you just detach and reattach the database…whether it is the same instance or not then by default CDC will remain enabled and all your meta data is preserved.
  • If you restore a database from backup on the same server and change the name of the database … CDC will be disabled by default.. which will result in you LOSING ALL YOUR DATA concerning CDC.
  • If you restore a database to a different instance … CDC will be disabled by default.. which will result in you LOSING ALL YOUR DATA concerning CDC.
  • If you are restoring in one of the previous two scenarios then you can use the KEEP_CDC option for the RESTORE command in order to maintain your CDC information. 
RESTORE DATABASE AdventureWorksLT

FROM DISK='C:\Backups\AdventureWorksLT.bak'

WITH KEEP_CDC

  • If you are trying to be sneaky and restore a CDC enabled database to another edition of SQL Server other than Enterprise or Developer Edition…. then you will start receiving you will start receiving an Error message 932 because your operation will be blocked. 
SQL Server cannot load database ‘%.*ls’ because change data capture is enabled. 
The currently installed edition of SQL Server does not support change data capture. 
Either disable change data capture in the database using a supported edition of 
SQL Server, or upgrade the instance to one that supports change data capture.

Now in regards to the last point. Even though CDC is not supported in anything other than Developer and Enterprise editions….the other versions do support the  sys.sp_cdc_disable_db command to disable it from the restored database. However, please remember that in doing so you are not just flipping a bit to disable the feature but you are telling SQL Server to go right ahead and delete all of that CDC data that you have been capturing!

Hopefully, this has helped un-cloud some of the mystery surrounding how these restores are handled when working with CDC.

 

Cheers!

AJ