SQL Server – How to Check if Backup File Contains CDC

change-data-capturesql serversql server 2014sql-server-2008sql-server-2012

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!

Best Answer

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

Related Question