SQL Server Restore Error – Could Not Upgrade Metadata

change-data-capturerestoresql serversql-server-2012

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

Best Answer

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

Related Solutions

Sql-server – Restoring database to UNC path on local drive

Sounds like "access is denied" - make sure that the SQL Server service account can access the share with both read and write (whether you can access the share is irrelevant). If you did not create a share named D that points to the root of D:, then your syntax is wrong - you need to use D$. However I recommend creating a share that is NOT in the root of a drive, and making sure that the SQL Server service account (again, not you unless you manually set the SQL Server service account to run as your Windows account) has read and write access. Then it would be:

MOVE N'test'     TO N'\\PC91\ShareName\Data\test.mdf',
MOVE N'test_log' TO N'\\PC91\ShareName\Log\test.ldf'

However I have to wonder why you would want to use a UNC path for this in the first place. It means your database files are more at risk because the share can be removed or become temporarily unavailable while SQL Server is running.

SQL Server 2005 to 2008 R2 Restore Error 15105 – How to Fix

"MSSQL.1" is not the directory for a SQL Server 2008 R2 instance.

You should verify the directory you are trying to place the database in. The default data path for a normal installation is: "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLServer\MSSQL\Data".

Edit

If this is indeed the path of the databases for your SQL Server 2008 R2 instance then you can verify this by running the following query:


SELECT name, physical_name
FROM sys.master_files
WHERE database_id = 1

You should get results similar to this, highlighted the path for my instance: enter image description here

You can also verify this path through the restore prompt in SSMS, clicking on the "files": enter image description here However also noting that the error message operating system returned the error '5, the 5 normally indicates permissions issue with the service account running the database engine. If the full message was provided you would see something like

The operating system returned the error '5(Access is denied)' while attempting 'RestoreContainer::ValidateTargetForCreation'

Related Question