October 6, 2022 at 12:23 pm
I am attempting to restore a database from a server in one domain to another. The Source server is 2014 (SP3-CU4-GDR) (KB5014164) - 12.0.6439.10 (X64) Dev edition and the destination server is the same, the only difference being that the source server has the DB as part of an AG and the destination has no AG. The process has been to take a Copy Only backup, copy it from the Azure file share to the local drive on the lower env. server to do the restore.
I have tried the "keep_CDC" switch in the restore command, no difference. I have verified the backup file and SQL says it's OK.
The restore ends with the DB in "Recovery Pending" state. Below is a clip of the query windows messages.
100 percent processed.
Processed 17440424 pages for database 'Medicare01_Premera', file 'Medicare01_Premera' on file 1.
Processed 805498 pages for database 'Medicare01_Premera', file 'Medicare01_Premera_log' on file 1.
Processed 0 pages for database 'Medicare01_Premera', file 'Medicare01_Premera_log2' on file 1.
Msg 3636, Level 16, State 1, Line 4
An error occurred while processing 'SysFiles1' metadata for database id 10 file id 3.
Msg 3167, Level 16, State 3, Line 4
RESTORE could not start database 'Medicare01_Premera'.
Msg 3167, Level 16, State 1, Line 4
RESTORE could not start database 'Medicare01_Premera'.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
SQL Log messages:
Starting up database 'Medicare01_Premera'.
Recovery is writing a checkpoint in database 'Medicare01_Premera' (10). This is an informational message only. No user action is required.
An error occurred while processing 'SysFiles1' metadata for database id 10, file id 3, and transaction='PostOfflineRestoreFixups'. Additional Context='(null)'. Location='container.cpp'(7770). Retry the operation; if the problem persists, contact the database administrator to review locking and memory configurations. Review the application for possible deadlock conflicts.
Now, After multiple attempts on the SQL 2014 server with no success, I tried a restore of the same backup file on a SQL 2016 server. It gave me the same messages at the end of the restore, but I found the DB online and I am able to access data in it.
Does anyone have any ideas about what I can do to get this restore to happen successfully on the SQL 2014 server?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 6, 2022 at 5:17 pm
Well, we found something that worked, but I am still unsure why. The DB had two log files. We did a backup the the logs then removed the second log file from the DB, created a new full backup and restored to the destination. This worked.
Having multiple log files is no reason for the restore to fail, so I am unsure what's wrong in the previous backup that caused this problem.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
October 6, 2022 at 7:26 pm
Perhaps something in the restore statement, but glad it's working and thanks for the followup.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply