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!

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
4
  • 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
    Commented May 5, 2015 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
    Commented May 5, 2015 at 15:48
  • Thanks Steve for the post and thanks @Kin, learned something new today.
    – Learning_Learning
    Commented Nov 29, 2016 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
    Commented Nov 29, 2017 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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