Detaching CDC

Question

Detaching CDC

Steve Jones - SSC Editor

SSC Guru

Points: 734459
More actions
April 11, 2018 at 8:07 pm

#336973

Comments posted to this topic are about the item Detaching CDC

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Stewart "Arturius" Campbell SSC Guru Points: 72499 More actions · Answer 1

Nice one, thanks Steve
Had to read up on this so learned something...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 2

But we are restoring on a new instance and not on the same one. Or am i understanding something wrong?

fritsvanruyven SSC Rookie Points: 41 More actions · Answer 3

The right explanation is "If a database is detached and attached to the same server or another server, change data capture remains enabled."

sestell1 SSChampion Points: 10230 More actions · Answer 4

fritsvanruyven - Thursday, April 12, 2018 2:21 AM
The right explanation is "If a database is detached and attached to the same server or another server, change data capture remains enabled."

Assuming the new instance is an Enterprise instance...

If a database is attached or restored with the KEEP_CDCKEEP_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.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.

gary.mazzone SSCarpal Tunnel Points: 4686 More actions · Answer 5

According to MS docs you need to add the KEEP_CDC if reattaching to a new instance of SQL Server also needs to be Enterprise Edition or it will fail also

roger.plowman SSChampion Points: 10265 More actions · Answer 6

So, by the question's own wording, the "correct" answer is actually wrong. The question says "new instance" the explanation says "same instance".

Badly constructed question!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

roger.plowman - Thursday, April 12, 2018 6:50 AM
So, by the question's own wording, the "correct" answer is actually wrong. The question says "new instance" the explanation says "same instance".
Badly constructed question!

Actually, the problem is that the explanation is truncated. Here is the complete part from BOL. The 3rd bullet is the one that applies.

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 934 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 restore database without KEEP_CDC option, or upgrade the instance to one that supports Change Data Capture.
You can use sys.sp_cdc_disable_db to remove change data capture from a restored or attached database.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

George Vobr SSCrazy Eights Points: 9917 More actions · Answer 8

sestell1 - Thursday, April 12, 2018 6:06 AM
fritsvanruyven - Thursday, April 12, 2018 2:21 AM
The right explanation is "If a database is detached and attached to the same server or another server, change data capture remains enabled."
Assuming the new instance is an Enterprise instance...
If a database is attached or restored with the KEEP_CDCKEEP_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.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.

Change Data Capture is supported in Standard Edition since SQL Server 2016 SP1. It's still unsupported in Web and Express editions.

George Vobr SSCrazy Eights Points: 9917 More actions · Answer 9

gary.mazzone - Thursday, April 12, 2018 6:29 AM
According to MS docs you need to add the KEEP_CDC if reattaching to a new instance of SQL Server also needs to be Enterprise Edition or it will fail also

An interesting explanation can be found in "Restoring or Moving a Database with CDC enabled".

gary.mazzone SSCarpal Tunnel Points: 4686 More actions · Answer 10

George Vobr - Thursday, April 12, 2018 7:27 AM
gary.mazzone - Thursday, April 12, 2018 6:29 AM
According to MS docs you need to add the KEEP_CDC if reattaching to a new instance of SQL Server also needs to be Enterprise Edition or it will fail also
An interesting explanation can be found in "Restoring or Moving a Database with CDC enabled".

But that appears to restoring or re-attaching to the same instance not a new one

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 11

Apologizes for the miswording in the explanation that left out the "new instance"

points awarded back.