(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.