Command line restore returns a 0 errorlevel on failure

jonwilkjonwilk Posts: 32
edited September 17, 2005 5:28AM in SQL Backup Previous Versions
Hi

I've found a problem using the command line restore. On this paritcular failure it returns a 0 error level which is meant to indicate a success.

I've pasted the results from the command prompt:

C:\Documents and Settings\jonathan>"C:\Program Files\Red Gate\SQL Backup\SqlBackupC.exe" -SQL "RESTORE DATABASE [TMAC] FROM DISK = 'D:\Rackspace Backups\DIFF_(local)_TMAC_20050915 230001.sqb' WITH STANDBY = 'D:\Rackspace Backups\UNDO_TMAC.DAT'" -E

SQL Backup 3.2.0, (c) Red Gate Software Ltd 2004 - 2005 Trial - 7 days remaining

Restoring database TMAC from "D:\Rackspace Backups\DIFF_(local)_TMAC_20050915 230001.sqb"

Msg 3136, Level 16, State 1, Server TMACBACKUP, Line 1
Cannot apply the backup on device 'SQLBACKUP_41222664' to database 'TMAC'.
Msg 3013, Level 16, State 1, Server TMACBACKUP, Line 1
RESTORE DATABASE is terminating abnormally.


C:\Documents and Settings\jonathan>echo %errorlevel%
0

Any ideas guys - you've got 7 days to impress me ;)

Jonny

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hi Jonny,

    Have you figured out the reason why this backup couldn't be restored? I've tried restoring a log backup to the wrong database, restoring an out-of-order transaction log, and attempting to restore a log to an on-line database. All of these operations set the errorlevel to 1010.

    If I we can figure out what kind of condition that needs to be trapped, we can see about adding it to the software.
  • Hi Brain

    To make the error occur just try and restore an incremental that's not in order.

    I took the following processes:

    1) Backup a full database on server 1
    2) Restore the full database on server 2 leaving the db in read only with an undo file.
    3) Create an incremental backup on server 1 after some data had been inserted (INC1)
    4) Create another incremental backup on server 1, again after some data had been inserted (INC2)
    5) Try and restore the second incremental backup (INC2) on server 2, leaving the database in readonly mode with an undo file.

    Both the databases on each server are in simple recovery mode.

    Let me know if you need any further information.

    Jonny
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Ah-ha!

    Because you cannot restore a transaction log backup to a database that has a SIMPLE recovery model. You should be able to restore differential backups, though.
  • peteypetey Posts: 2,358 New member
    7 days is probably inadequate!

    SQL Backup 3.x cannot report logical SQL errors during the backup/restore process. It can only report errors it encounters itself.

    In SQL Backup 4.x, SQL errors will be reported. Syntax would look similar to this:

    DECLARE @sqbexitcode int
    DECLARE @sqlerrorcode int
    exec master..sqlbackup '-sql "BACKUP ..."', @sqbexitcode OUTPUT, @sqlerrorcode OUTPUT

    In your case, sqlerrorcode will store the value 3136.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • the problem i'm having is that it's returning an error code of 0 when it in facts fails. I'm trying to automate a restore process and if i can't get a negative error code when it fails then it's no use to me i'm afraid. I know why it fails i just need it to tell it me it fails when it does

    When's version 4 coming out?
Sign In or Register to comment.