Taking database out of Restoring state

jwilleyjwilley Posts: 23
edited November 5, 2008 11:04AM in SQL Backup Previous Versions
My log shipping restore job died with a corrupted file. I want to take the database out of the restoring state -- I use the command RESTORE DATABASE [dbname] WITH RECOVERY and get the following error:
A previous restore operation was interrupted and did not complete processing on file 'dbname'. Either restore the backup set that was interrupted or restart the restore sequence.
I'm stuck from here -- any suggestions?

thanks -- john
· Share on Twitter

Comments

  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi John,

    What error message did you encounter when originally trying to restore the corrupted file?

    Did you try converting it to Native format using our sqb2mtf tool and restoring again?

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • jwilleyjwilley Posts: 23
    Here are the error log entries. I did attempt to convert the file using sqd2mtf and got the same messages. Unfortunately by the time I saw the problem, the original file on the source server had been deleted (was more than 24 hours later) so recopying it to the destination server was no longer an option.

    10/27/2008 8:40:39 AM: Moved A64-Prod_LOG_(local)_20081026_011500.sqb to C:\MSS_DB_Backups\.
    10/27/2008 8:40:39 AM: Restoring A100-Prod (transaction logs) from:
    10/27/2008 8:40:39 AM: D:\A64ShippedLogs\A64-Prod_LOG_(local)_20081026_014501.sqb

    10/27/2008 8:40:40 AM: RESTORE LOG [A100-Prod] FROM VIRTUAL_DEVICE = 'SQLBACKUP_70A3D603-96C2-4273-A011-4F2CE2944F2D', VIRTUAL_DEVICE = 'SQLBACKUP_70A3D603-96C2-4273-A011-4F2CE2944F2D01', VIRTUAL_DEVICE = 'SQLBACKUP_70A3D603-96C2-4273-A011-4F2CE2944F2D02', VIRTUAL_DEVICE = 'SQLBACKUP_70A3D603-96C2-4273-A011-4F2CE2944F2D03', VIRTUAL_DEVICE = 'SQLBACKUP_70A3D603-96C2-4273-A011-4F2CE2944F2D04', VIRTUAL_DEVICE = 'SQLBACKUP_70A3D603-96C2-4273-A011-4F2CE2944F2D05', VIRTUAL_DEVICE = 'SQLBACKUP_70A3D603-96C2-4273-A011-4F2CE2944F2D06' WITH BUFFERCOUNT = 28, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY

    10/27/2008 8:42:34 AM: Thread 0 error:
    Process terminated unexpectedly. Error code: -2139684860
    10/27/2008 8:42:34 AM:
    10/27/2008 8:42:34 AM: Thread 1 error:
    File read error for compressed data. Backup file is incomplete or corrupted (FileBufferRemainingBytes: 0)
    Failed to service command. Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860
    10/27/2008 8:42:34 AM:
    10/27/2008 8:42:34 AM: Thread 2 error:
    File read error for compressed data. Backup file is incomplete or corrupted (FileBufferRemainingBytes: 0)
    Failed to service command. Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860
    10/27/2008 8:42:34 AM:
    10/27/2008 8:42:34 AM: Thread 3 error:
    File read error for compressed data. Backup file is incomplete or corrupted (FileBufferRemainingBytes: 0)
    Failed to service command. Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860
    10/27/2008 8:42:34 AM:
    10/27/2008 8:42:34 AM: Thread 4 error:
    File read error for compressed data. Backup file is incomplete or corrupted (FileBufferRemainingBytes: 0)
    Failed to service command. Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860
    10/27/2008 8:42:34 AM:
    10/27/2008 8:42:34 AM: Thread 5 error:
    File read error for compressed data. Backup file is incomplete or corrupted (FileBufferRemainingBytes: 0)
    Failed to service command. Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860
    10/27/2008 8:42:34 AM:
    10/27/2008 8:42:34 AM: Thread 6 error:
    File read error for compressed data. Backup file is incomplete or corrupted (FileBufferRemainingBytes: 0)
    Failed to service command. Error code: -2139684860
    Process terminated unexpectedly. Error code: -2139684860
    10/27/2008 8:42:34 AM:
    SQL error 3013: SQL error 3013: RESTORE LOG is terminating abnormally.
    SQL error 3203: SQL error 3203: Read on "SQLBACKUP_70A3D603-96C2-4273-A011-4F2CE2944F2D" failed: 995(The I/O operation has been aborted because of either a thread exit or an application request.)
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi,

    It looks like you will need to either restore the full backup and all subsequent transaction log backups if you have them up until the corrupted file or reseed the database with a full backup if this is a log shipping scenario.

    I apologize you encountered this problem.
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
  • jwilleyjwilley Posts: 23
    So, you're saying that if a log file is corrupted, there is no way to salvage any of the data. The database cannot be taken out of the restoring state because it thinks a restore is in progress and there is nothing to do but delete the database. That's pretty drastic -- but ok.
    · Share on Twitter
  • Matthew FlattMatthew Flatt Posts: 255 Bronze 5
    Hi,

    Have you tried restoring/copying over the local copy of that transaction log backup to see if it's also corrupted?

    If you wish to continue log shipping and have a corrupted backup with no other copies, the database will need to be reseeded.

    You could try restoring the log to a point in time, it may work but I am unsure if SQL Server goes through the whole restore and then rolls back to that point or only restores up to that point and then stops.

    If it's the latter and your file is corrupted part way through you may be able to restore to before that corruption point and have the database available.

    Thanks,
    Matthew Flatt
    Redgate Foundry
    · Share on Twitter
Sign In or Register to comment.