I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
You should use the option -b in sqlcmd.
-b Specifies that sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs. The value that is returned to the DOS ERRORLEVEL variable is 1 when the SQL Server error message has a severity level greater than 10; otherwise, the value returned is 0
From the MSDN SQLCMD Utility page at: http://msdn.microsoft.com/en-us/library/ms162773.aspx
If RAISERROR is used within a sqlcmd script and a state of 127 is raised, sqlcmd will quit and return the message ID back to the client. For example:
RAISERROR(50001, 10, 127)
So you could wrap the
BACKUP DATABASE... command in a
TRY...CATCH block and raise the appropriate error message, which
sqlcmd would then return to your batch file.
For instance, the consider the following
:ON ERROR EXIT BEGIN TRY /* creates error 3147 Backup and restore operations are not allowed on database tempdb */ BACKUP DATABASE tempdb; END TRY BEGIN CATCH DECLARE @msg NVARCHAR(255); SET @msg = 'An error occurred: ' + ERROR_MESSAGE(); RAISERROR (50002, 10, 127); END CATCH
And the following .bat file: (first line is wrapped for readability, but needs to be on a single line.)
@echo off "C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd" -S "someinstance" -E -i .\errorleveltest.sql ECHO Errorlevel: %ERRORLEVEL%
This returns the following from my cmd.exe prompt:
Msg 18054, Level 16, State 1, Server CP708-D377\MV, Line 9 Error 50002, severity 10, state 127 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage. Errorlevel: 1
As you can see, ERRORLEVEL 1 is returned instead of the normal return value of 0. I'm unable to get the ERRORLEVEL to reflect the actual error number, in this case 50002; perhaps there is an issue in my code, or perhaps there is some problem with my environment.