March 8, 2011 at 3:09 pm
We have an issue with our dev and qa environments. We do snaps from prod using EMC to source our DEV and QA environments daily. Some of the dbs in Production have CDC enabled on them and some do not. When the DBS are dropped and Recreated with For Attach pointing to the snapped data files. This works perfectly with one exception. The bit [is_cdc_enabled] = 0 on ALL the dbs in sys.databases even those that actually have cdc enabled in production. This leaves those dbs in a state where..
You can not enable CDC because the CDC schema exists already on the Db...
declare @rc int
exec @rc = sys.sp_cdc_enable_db
select @rc
Msg 22906, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 49
The database 'XXXXXXX' cannot be enabled for Change Data Capture because a database user named 'cdc' or a schema named 'cdc' already exists in the current database. These objects are required exclusively by Change Data Capture. Drop or rename the user or schema and retry the operation.
And you can not disable CDC because the it does not see the Bit as being set...
declare @rc int
exec @rc = sys.sp_cdc_Disable_db
select @rc
The database 'XXXXXX' is not enabled for Change Data Capture. Ensure that the correct database context is set and retry the operation. To report on the databases enabled for Change Data Capture, query the is_cdc_enabled column in the sys.databases catalog view.
When trying to follow the recommendations of the first error about removing the cdc schema we find there are several cdc objects that can not be dropped.
Has anyone found a way to attach a CDC enabled db to another instance and have it function properly or at least be in a state that is workable.
My main issue is I need to test new CDC in dev and QA and can't set up additional tables for testing in those dbs that have CDC in production.
I found http://msdn.microsoft.com/en-us/library/cc645938.aspx
which refers to KEEP_CDC but I can find no way to use this with Create For Attach..
June 29, 2012 at 12:37 pm
Hi
were you able to fix it? I have also run into same situation
Appriciate your help Thanks,
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply