Msg 22841, Level 16, State 1, Procedure sp_cdc_vupgrade, Line 323 – While restoring from a cdc enabled database backup

We encountered an issue as below while restoring from a CDC enabled database backup.

Though Management Studio has shown the above message, the backup restore was successful. To further understand, we tried to restore the database using T-SQL, but ended with the same error.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 22841, Level 16, State 1, Procedure sp_cdc_vupgrade, Line 323
Could not upgrade the metadata for database ‘CDCTest’ 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.
RESTORE DATABASE successfully processed 1058229 pages in 72.909 seconds (113.393 MB/sec).

This error message was more clear that the restore database was successful.

In addition, we found that Microsoft Knowledge Base article KB2567366 describes a mechanism for skipping the internal “CDC Upgrade” process by enabling the trace -T3101. When the -T3101 trace flag is used, the restore process bypasses a CDC upgrade operation that is associated with this issue.


DBCC TRACEON (3101, -1)

RESTORE DATABASE ArchivalTest_1 FROM DISK = 'C:\bkpfile.bak'
WITH MOVE 'bkpfile' TO 'C:\data\bkpfile.mdf'
    , MOVE 'bkpfile_log' TO 'C:\data\bkpfile.LDF'
    , REPLACE
    , KEEP_CDC;

DBCC TRACEOFF (3101, -1)

The above query has executed successfully without any error message!!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s