Command line restore returns a 0 errorlevel on failure
jonwilk
Posts: 32
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
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
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.
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
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.
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
When's version 4 coming out?