Troubleshooting
Problem
Attempts to restore a Microsoft SQL Server database with Change Data Capture (CDC) enabled to an alternate location result in failures
Symptom
The restore operation, upon processing the last object and attempting to activate the database, returns messages similar to the following:
An exception occurred while executing a Transact-SQL statement or batch. Could not update the metadata that indicates database <Alternate Database Name> is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_drop_objects]'. The error returned was 3729: 'Cannot drop schema 'cdc' because it is being referenced by object '<Object Name>'
Environment
CDC enabled Microsoft SQL Databases
Diagnosing The Problem
Review the tdpsql.log after the restore failure for messages similar to the above, and validate that the source database has CDC enabled.
Resolving The Problem
At this time, the restore of a CDC enabled database to an alternate location is not supported with the IBM Spectrum Protect for Databases: Data Protection for Microsoft SQL Server application.
This is because the target database created during the restore process will not have CDC enabled, and the Data Protection application does not support the 'KEEP_CDC' SQL function.
As a work around, the alternate restore can be performed with the /recovery=no option. This will allow the restore to an alternate location but will not be activated. Then the database can be enabled for CDC and activated within SQL Server Management.
Refer to the related URL for additional Microsoft information on restoring a SQL database which has CDC enabled.
Related Information
Product Synonym
TSM
Was this topic helpful?
Document Information
Modified date:
17 June 2018
UID
swg22009330