0

I am trying to restore a database from a Prod environment (11.0.6020.0) to a lower environment server (11.0.3128.0). I realize the lower has an older SP, we are working on correcting that now. The restore should still work though, it has for months, but I'm getting the following error message. We just recently enabled this database for CDC, I'm not specifying keep_cdc in the restore. Anybody seen this error before and know how to resolve it?

Msg 22841, Level 16, State 1, Procedure sp_cdc_vupgrade, Line 320 [Batch Start Line 0] Could not upgrade the metadata for database 'DBName' 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 [DBName] 
FROM  DISK = @FileToRestore WITH  
MOVE  'DBName' to 'W:\Data\DBName\DBName.mdf',
MOVE  'AmexDB_log' to 'W:\Logs\DBName\DBName_Log.ldf',
NOUNLOAD, REPLACE , FILE = 1, STATS = 10
GO
Improve this question
6
  • Is the lower environment Enterprise Edition too?
    – Tara Kizer
    Commented Feb 8, 2017 at 16:21
  • Yes, it is running Enterprise as well.
    – MrTCS
    Commented Feb 8, 2017 at 16:26
  • Did you try this solution on prod? blogs.msdn.microsoft.com/ramoji/2009/11/19/…
    – Tara Kizer
    Commented Feb 8, 2017 at 16:30
  • We were able to clear all the devs off of that server and are installing SP3 now, we shall see soon if that fixes it. Still seems like an error that shouldn't occur though.
    – MrTCS
    Commented Feb 8, 2017 at 16:31
  • I checked and SA was already the owner in production. The upgrade finished and that error went away. I'm wondering if during the restore CDC is restored, then dropped if keep_cdc is not used.
    – MrTCS
    Commented Feb 8, 2017 at 16:41

1 Answer 1

Reset to default
0

We were able to resolve the issue by upgrading the lower environment to SP3. Once that was done the restore completed without any errors.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.