Skip to main content
commvault.com commvault.com
Solved

SQL Server restoring stuck at 95%

  • October 14, 2022
  • 5 replies
  • 2604 views
M
Byte
Forum|alt.badge.img+4

Hi,

We are restoring big SQL Server database - almost 10TB. But at 95% task failed with error:

Query Result [Microsoft.SqlServer.Management.Common.ExecutionFailureException:Could not upgrade the metadata for database '***' that is enabled for Change Data Capture. The failure occurred when executing the action 'sp_cdc_create_populate_stored_procs'. The error returned was 208: 'line 1, state 6, Invalid object name 'cdc.sp_insdel_1125019189'.'. Use the action and error to determine the cause of the failure and resubmit the request. RESTORE DATABASE successfully processed 0 pages in 175.387 seconds (0.000 MB/sec). An exception occurred while executing a Transact-SQL statement or batch.].

After resume pending job next error was:

Query Result [Microsoft.SqlServer.Management.Common.ExecutionFailureException:The backup or restore was aborted. RESTORE DATABASE is terminating abnormally. Cannot continue the execution because the session is in the kill state. A severe error occurred on the current command. The results, if any, should be discarded. An exception occurred while executing a Transact-SQL statement or batch.].

 

And after that, every next resume was failed with error:

There is nothing to restore, database [***] is already up to date.

 

We had exatcly the same problem few months ago and restoring just from full (no transactional log) backup helped. But now we tried to do the same and no result. DB is in HA cluster which is configured in Commvault. Any ideas?

Best answer by Ron Potts

Hello @mateusznitka 

Looking at the failure it mentions:
“Could not upgrade the metadata for database '***' that is enabled for Change Data Capture.”

Can you confirm: 

  • Is this an In-Place or Out-of-Place restore of the database?
  • If Out-of-Place -- Is the Source SQL Server (or database) version different than the version of SQL installed on the destination?

Its hard to say for sure with out the logs but thinking that at 95% it could be attempting to bring the database online after the data has been restored. 

Reading through the failure, it would appear that something with the CDC feature enabled on the database itself is what is trigging the failure when attempting to bring the database online and possibly when attempting to upgrade the database (if restoring to a newer version of SQL). It could be some failure related to this CDC feature when attempting to restore from a lower version of SQL to a higher version when it attempts to update the database. 

https://social.msdn.microsoft.com/Forums/onedrive/en-US/f0c5de0d-2333-43c2-88cb-bdae538192b3/error-during-upgrading-cdc-enabled-databases-from-sql-2008-r2-to-sql-2012?forum=databasedesign

You could attempt to restore the database to disk in Commvault and attempt to import it natively into SQL:

https://documentation.commvault.com/2022e/essential/104483_restoring_sql_database_to_disk.html

Hope this helps!

Thank you,

Ron Potts

View original
Did this answer your question?

5 replies

R
Vaulter
Forum|alt.badge.img+7
  • Ron PottsVaulter
  • Vaulter
  • 32 replies
  • Answer
  • October 14, 2022

Hello @mateusznitka 

Looking at the failure it mentions:
“Could not upgrade the metadata for database '***' that is enabled for Change Data Capture.”

Can you confirm: 

  • Is this an In-Place or Out-of-Place restore of the database?
  • If Out-of-Place -- Is the Source SQL Server (or database) version different than the version of SQL installed on the destination?

Its hard to say for sure with out the logs but thinking that at 95% it could be attempting to bring the database online after the data has been restored. 

Reading through the failure, it would appear that something with the CDC feature enabled on the database itself is what is trigging the failure when attempting to bring the database online and possibly when attempting to upgrade the database (if restoring to a newer version of SQL). It could be some failure related to this CDC feature when attempting to restore from a lower version of SQL to a higher version when it attempts to update the database. 

https://social.msdn.microsoft.com/Forums/onedrive/en-US/f0c5de0d-2333-43c2-88cb-bdae538192b3/error-during-upgrading-cdc-enabled-databases-from-sql-2008-r2-to-sql-2012?forum=databasedesign

You could attempt to restore the database to disk in Commvault and attempt to import it natively into SQL:

https://documentation.commvault.com/2022e/essential/104483_restoring_sql_database_to_disk.html

Hope this helps!

Thank you,

Ron Potts

Brian Butler
M
2 people like this
  • Brian Butler
    Brian Butler
  • M
    mateusznitka
M
Byte
Forum|alt.badge.img+4
  • mateusznitka
  • Author
  • Byte
  • 10 replies
  • October 17, 2022

Hello,

Thank you for reply. This is second option you mentioned: out of place restoring from SQL Server 2012 version 11.0.7001 to 11.0.7507.2. I will try to restore it to disk or ask DB admins about upgrading SQL Server version.

Thanks for clue. I’ll let you know.

M
Byte
Forum|alt.badge.img+4
  • mateusznitka
  • Author
  • Byte
  • 10 replies
  • October 21, 2022

We did restore to disk and try to add DB by SQL Studio. The same error appears

But anyway DB seems to work fine. 

R
1 person likes this
  • R
    Ron Potts
S
Vaulter
Forum|alt.badge.img+14

@mateusznitka 

This looks to be a MSSQL issue directly related to Change Data Capture.

There are some links on the web for various fixes and a MSFT article.

Can you attempt to perform the restore adding keep_cdc at the end if restoring from disk in SQL.

Here is web article about same above:

https://www.sqlservercentral.com/articles/restore-a-database-with-cdc-enabled

There is also an option in CV to leverage this additional parameter.

https://documentation.commvault.com/2022e/expert/61506_advanced_restore_options_options.html

Otherwise, you might have to leverage the DBAs and open a case with MSFT.

R
M
2 people like this
  • R
    Ron Potts
  • M
    mateusznitka
M
Byte
Forum|alt.badge.img+4
  • mateusznitka
  • Author
  • Byte
  • 10 replies
  • November 29, 2022

Hi, just for update - we did advanced restore with keep change data capture option but result was unfortunately the same.

So workaround is restore to file and import DB ignoring the error.

We’ll see what support will say. Anyway, thanks for help.

Reply


Most helpful members this week

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings