36

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.

  • Have you considered using something more robust (particularly at error handling) than batch files? – Aaron Bertrand Sep 22 '14 at 16:50
  • Oh you mean like Powershell? We were just going with the company recommended method for the product we are using. Trouble is the recommend method assumes you're backing up one database; but I've overcome that hurdle. So there's no way to get it to just throw an error code if the backup doesn't work? – leeand00 Sep 22 '14 at 17:00
  • 3
    For what it's worth, at my current job, we use Ola Hallengren's backup stored procedures (with some in-house wrapper stored procedures around them) to backup thousands of databases across hundreds of servers, and we've had no problems with them. – James L Sep 22 '14 at 18:10
52

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

http://msdn.microsoft.com/en-us/library/ms162773.aspx

9

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 errorleveltest.sql file:

: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.

  • 1
    I've found that this ONLY works if you supply a numeric value as the first parameter of RAISERROR. for example: This returns a %errorlevel% = 1: RAISERROR(50002, 10,127) But this returns %errorlevel% = 0: RAISERROR('myErrMsg.', 10,127) – user73342 Aug 21 '15 at 20:43
  • 5
    FYI regarding the last paragraph: ERRORLEVEL shouldn't be expected to be the same value as the Error Number reported from SQL Server. The ErrorNumber is a SQL Server-specific value to indicate why it (i.e. SQL Server) terminated. On the other hand, ERRORLEVEL is a SQLCMD-specific value to indicate why it (i.e. SQLCMD) terminated. – Solomon Rutzky Dec 4 '15 at 19:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.