"System.Data.SqlClient.SqlError: Could not upgrade the metadata for database that is enabled for Change Data Capture." while creating images from backup
Published 23 October 2019
Creating a new image from a backup using a SQL Server with the Change Data Capture (CDC) feature may fail with an error:
System.Data.SqlClient.SqlError: Could not upgrade the metadata for database that is enabled for Change Data Capture. The failure occurred when executing the action 'alter cdc.change_tables index change_tables_unique_idx with (drop_existing = on)'.
In that case you can temporarily disable the CDC upgrade operation using a trace flag (see Microsoft KB2567366). To do so execute the following SQL script as a user with sysadmin privileges:
DBCC TRACEON (3101, -1)
After creating the image you can enable the CDC upgrade operation again:
DBCC TRACEOFF (3101, -1)