Error While Restoring a Backup on a different Server

jvenablesjvenables Posts: 6 New member
in SQL Backup

Hi All, having weird issues between 2 servers. Server A & B are SQL 2016 SP2 CU10. Both are set to SIMPLE recovery model.

Redgate SQL Backup Software Version - 10.0.7.1063

When I take a backup of a DB on Server A, copy and then try to restore onto a newly created blank DB on Server B I get the below error

I have restored the backup onto the Source DB just to prove there isn't an issue with the backup taken and that was successful

Server B: Restore - Failed ===========================   Restoring full backup - Failed ------------------------------   This operation failed with errors.   Restoring TestDatabase (database) from: V:\Transfer\FULL_TestDatabase_20200908_173343.sqb Thread 0 error: Failed to service command.  Error code: -2139684860 Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.) Thread 1 error: Failed to service command.  Error code: -2139684860 Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.) Thread 2 error: Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.) Thread 3 error: Failed to service command.  Error code: -2139684860 Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.) Thread 4 error: Failed to service command.  Error code: -2139684860 Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.) SQL error 3013: RESTORE DATABASE is terminating abnormally. SQL error 3183: RESTORE detected an error on page (65462:8388608) in database "TestDatabase" as read from the backup set. SQL Backup exit code: 790 SQL error code: 3183
Any guidance would be appreciated

Tagged:
· Share on Twitter

Answers

  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi jvenables, Thank you for your forum post.

    The error causing the restore to fail is being generated by SQL Server:
    SQL error 3183: RESTORE detected an error on page (65462:8388608) in database "TestDatabase" as read from the backup set.

    SQL error 3013: RESTORE DATABASE is terminating abnormally. Is a generic error code to indicate something unexpected occurred.

    The SQL Backup error 790,  is generated as a result of the SQL error 3183 error.

    SQL Error 3183, in most circumstances is caused by a corrupt backup file.  However, you have confirmed that the backup file is OK by restoring a copy of the backup on the source server (Server A) and I quote:
    I have restored the backup onto the Source DB just to prove there isn't an issue with the backup taken and that was successful
    Prior to this, you mention that you copied the backup before restoring on Server B.  How was the backup file copied?  I ask this question, as I have known SQL Backup sqb files to corrupt when copied using the Windows File Explorer. 

    If you copy the backup file using Robocopy  or something similar, I suspect the copying process will not corrupt the copied backup and you will then be able to restore the backup file on Server B.

    Many Thanks
    Eddie

    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • jvenablesjvenables Posts: 6 New member

    Eddie,


    Thanks for the update, we have been using these 2 servers for many months and a lot of other DB servers for many years, copying DB's between them using Windows file explorer. I tried a different DB this morning, which was dramatically smaller to be fair, less than 1Gb compared to the one mentioned above which has a backup file of 6GB.

    The DB backup this morning was performed on the same servers listed above and it restored 1st time with no issues, do you think it could be size related?


    Cheers



    · Share on Twitter
  • jvenablesjvenables Posts: 6 New member
    edited September 10, 2020 10:45AM

    Eddie,

    I performed an xcopy of the backup from the source server to the target and tried another restore which has failed again.

    Couple of things to mention, the DB is now showing as stuck in Restoring Mode.

    When I got to the drive where the MDF is, it is now showing the full size of the MDF file which is 146GB, so looks like it has completed most of the restore, but just not able to complete

    Log

    TestDBServer: Restore - Failed
    ===========================

    Restoring full backup - Failed
    ------------------------------

    This operation failed with errors.

    Restoring TESTDatabase (database) from:
    V:\Transfer\TESTDatabase.sqb
    Thread 0 error:
    Failed to service command.  Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
    Thread 1 error:
    Failed to service command.  Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
    Thread 2 error:
    Failed to service command.  Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
    Thread 3 error:
    Failed to service command.  Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
    Thread 4 error:
    Failed to service command.  Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)
    SQL error 3013: RESTORE DATABASE is terminating abnormally.
    SQL error 3183: RESTORE detected an error on page (22:347356) in database "TESTDatabase" as read from the backup set.
    SQL Backup exit code: 790
    SQL error code: 3183

    Updating SQL Server information - Pending
    -----------------------------------------

    Operation pending.



    · Share on Twitter
  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    edited September 15, 2020 10:01AM
    Hi jvenables,
    Thank you for your replies.

    The size of the database being restored or the the size of the backup file should not matter here, unless there is insufficient disk space to create the mdf file of the restoring database.  Although I would have expected a different error message if there was insufficient disk space to restore the backup file and for it to occur near the beginning of the restore process.

    I have created a support ticket for you, so I can request additional information and files that you may not wish to make public via this forum.

    Couple of things to mention, the DB is now showing as stuck in Restoring Mode.
    When I got to the drive where the MDF is, it is now showing the full size of the MDF file which is 146GB, so looks like it has completed most of the restore, but just not able to complete
    The database does remain in a Restoring mode when the restore process fails.  You can attempt to recovery the database by using this command:

    USE master
    GO
    RESTORE DATABASE [database_name] WITH RECOVERY
    GO

    I will contact you via the support ticket created for you, whose reference number is 168808.

    Many Thanks
    Eddie

    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.