SQL Server with Change Data Capture (CDC) Enabled May Cause Point-In-Time Refresh to Fail (KBA1676)
KBA
KBA#1676Issue
When refreshing a Microsoft SQL Server VDB using Point in Time recovery, the action may fail with the error message:
Refresh database "EFT".
Error
Recovery failed for virtual database "EFT"
Error Code
exception.db.mssqlvdb.vdb.restore.failed
Suggested Action
Make sure that the SQL Server instance "YOUR_INSTANCE" on the target host "YOUR_SERVER" is up and that the user "YOU
Troubleshooting Change Data Capture (CDC) Feature
This error may be related to the use of the Change Data Capture (CDC) feature of SQL Server.
If this is the case, the Command Output shown by the Action error will mention Change Data Capture, as shown below:
Command Output ... RESTORE DATABASE [YOUR_DB] WITH NORECOVERY RESTORE DATABASE successfully processed 0 pages in 0.448 seconds (0.000 MB/sec). Changed database context to 'master'. Processed 0 pages for database 'YOUR_DB', file 'YOUR_DB' on file 1. Processed 12 pages for database 'YOUR_DB', file 'YOUR_DB_log' on file 1. Msg 22841, Level 16, State 1, Server YOUR_SERVER, Procedure sp_cdc_vupgrade, Line 320 Could not upgrade the metadata for database 'YOUR_DB' 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)'. The error returned was 4922: 'line 102, state 9, ALTER TABLE ALTER COLUMN column_id failed because one or more objects access this column.'. Use the action and error to determine the cause of the failure and resubmit the request.
Resolution
See Microsoft Knowledge Base article KB2567366 which describes a mechanism for skipping the internal "CDC Upgrade" process that triggers this error.
On the target environment experiencing this issue, enable Trace Flag 3101 by executing the following SQL as a user with sysadmin privileges:
DBCC TRACEON (3101, -1)
Once the Refresh completes without error, the feature can be turned off again using the following SQL:
DBCC TRACEOFF (3101, -1)
Updating to the latest available SQL Server Service Pack or Cumulative Update may also resolve this issue.