question

cornpoppy avatar image
cornpoppy asked

Error message in restoring database

I saw this error message occurs during a restore operation, I check database integrity in source server with Ola Hallengren script, database is OK ![alt text][1] ![alt text][2] [1]: /storage/temp/3779-error.png [2]: /storage/temp/3780-untitled.png
backupdbarestoredba-developererror-log
error.png (145.1 KiB)
untitled.png (102.9 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
Are you using replication or CDC ?
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

cornpoppy avatar image cornpoppy commented ·
I check all database with this query SELECT [name], database_id, is_cdc_enabled FROM sys.databases where is_cdc_enabled=1 and yes , is_cdc_enabled is true for all databases that have this problem
0 Likes 0 ·
cornpoppy avatar image cornpoppy commented ·
Thank you so much ,I could restore . RESTORE DATABASE My_DB FROM DISK = 'D:\My_DB.bak' WITH MOVE 'My_DB_Data' TO 'D:\Data\My_DB_Data.mdf', MOVE 'My_DB_Data_New_01' TO 'D:\Data\My_DB_Data_New_01.ndf', MOVE 'My_DB_Data_Current_01' TO 'D:\Data\My_DB_Data_Current_01.ndf', MOVE 'My_DB_Data_Current_02' TO 'D:\Data\My_DB_Data_Current_02.ndf', MOVE 'My_DB_Log' TO 'F:\Log\My_DB_Log.ldf' ,keep_cdc,NOUNLOAD, STATS = 10
0 Likes 0 ·
Wilfred van Dijk avatar image Wilfred van Dijk commented ·
In addition, you need to add the Capture and Cleanup jobs in the appropriate database. See: https://www.mssqltips.com/sqlservertip/2421/restoring-a-sql-server-database-that-uses-change-data-capture/
0 Likes 0 ·
rvsc49 avatar image
rvsc49 answered
What version of Sql Server is this? The reason asking is that some received this error due to a bug which required a service pack and/or cumulative update.
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image Wilfred van Dijk commented ·
MSSSQL 12 which is SQL2014 (first line in errorlog)
0 Likes 0 ·
cornpoppy avatar image cornpoppy commented ·
Microsoft SQL Server 2014 (SP2)
0 Likes 0 ·
DazyParker avatar image
DazyParker answered
Here is the solution of this issue: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/90386e05-a84d-407e-a840-e684d83a6728/attempt-to-restore-a-db-fails?forum=sqldatabaseengine
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.