April 11, 2018 at 8:07 pm
Comments posted to this topic are about the item Detaching CDC
April 11, 2018 at 11:00 pm
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”
April 12, 2018 at 1:06 am
But we are restoring on a new instance and not on the same one. Or am i understanding something wrong?
April 12, 2018 at 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."
April 12, 2018 at 6:06 am
fritsvanruyven - Thursday, April 12, 2018 2:21 AMThe 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.
April 12, 2018 at 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
April 12, 2018 at 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!
April 12, 2018 at 6:58 am
roger.plowman - Thursday, April 12, 2018 6:50 AMSo, 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.
April 12, 2018 at 7:02 am
sestell1 - Thursday, April 12, 2018 6:06 AMfritsvanruyven - Thursday, April 12, 2018 2:21 AMThe 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.
April 12, 2018 at 7:27 am
gary.mazzone - Thursday, April 12, 2018 6:29 AMAccording 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".
April 12, 2018 at 8:58 am
George Vobr - Thursday, April 12, 2018 7:27 AMgary.mazzone - Thursday, April 12, 2018 6:29 AMAccording 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 alsoAn 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
April 12, 2018 at 9:49 am
Apologizes for the miswording in the explanation that left out the "new instance"
points awarded back.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply