9

Using SQL Server 2008 through to 2014, I know how to backup and restore a database with Change Data Capture enabled (CDC), but what I am looking for is a way to query a full backup file to determine if it contains CDC data.

What would be great would be a way like RESTORE FILELISTONLY that gets the file list from the database, but instead gets the CDC status to determine if the restore will need to use the KEEP_CDC flag?

Is there a way to determine if a backup contains CDC data?

thanks!

7

(if someone finds a way - which I dont think exists, I will delete my answer :-))

Is there a way to determine if a backup contains CDC data?

I dont think that there is any way to know from a backup, if CDC is enabled or not.

The most you can know if the database was involved in replication (under the hood uses log reader agent that scans the Transaction log) or not by using DatabaseBackupLSN in the result of RESTORE HEADERONLY DatabaseBackupLSN as is the “begin of checkpoint” that is triggered when the backup starts. This LSN will coincide with FirstLSN if the backup is taken when the database is idle and no replication is configured.

I assume the reason that microsoft does not surface whether CDC is enabled or not, might be a security thing as well. CDC is an enterprise level technology that enables tracking of changes all the way down to the column level. This is required by regulations like HIPAA, FISMA, etc.

From Restoring or Attaching a Database Enabled for Change Data Capture

SQL Server uses the following logic to determine if change data capture remains enabled after a database is restored or attached:

  • If a database is restored to the same server with the same database name, change data capture remains enabled.
  • If a database is restored to another server, by default change data capture is disabled and all related metadata is deleted.
  • To retain change data capture, use the KEEP_CDC option when restoring the database. For more information about this option, see RESTORE.
  • If a database is detached and attached to the same server or another server, change data capture remains enabled.
  • If a database is attached or restored with the KEEP_CDC option to any edition other than Enterprise, the operation is blocked because change data capture requires SQL Server Enterprise. Error message 932 is displayed:

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 by using a supported edition of SQL Server, or upgrade the instance to one that supports change data capture.

  • Kin - As usual, you are a rockstar DBA.Thanks for the details. I think I had come to the same conclusion, and was really looking for someone to validate that there isn't a way just check if a backup contains CDC. Have a great day. Thanks! – Steve Stedman May 5 '15 at 15:33
  • @SteveStedman Thanks for kind words. Please upvote / accept the answer if your question is answered. This will help future readers as well :-) – Kin Shah May 5 '15 at 15:48
  • Thanks Steve for the post and thanks @Kin, learned something new today. – SQLBoy Nov 29 '16 at 8:46
  • Note: CDC is now available on 2016SP1 Standard or above, and becoming more mainstream. I'd like CDC options in the restore UI on SSMS, I suggested this on connect too: connect.microsoft.com/SQLServer/feedback/details/3144490/… – GilesDMiddleton Nov 29 '17 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.