SQL Backup returned zero when it failed

gaffer

Executing SQL Backup in CMD file from SQL job. Job history shows "Process Exit Code 0. The step succeeded."

Event Log shows "BackupVirtualDeviceFile::RequestDurableMedia: Flush failure on backup device 'SQLBACKUP_2601361302'. Operating system error 995"

The CMD file contains the following code to check for error:
IF ERRORLEVEL 1 GOTO ERREXIT
EXIT 0
:ERREXIT
EXIT 1

By returning zero, the job continued to run when it should've failed.

Any suggestions?

:?:

Brian Donahue

Hello,

The batch file commands are absolutely correct. If there was an exit code returned by SQL Backup, the CmdExec job step should report failure, provided the success code is set to 0 on the job configuration in SQL Server. I'd run a few tests: backing up a database that doesn't exist, trying to back up to an existing file without specifying INIT (which trips an OS error as opposed to a VDI error), and the job says it failed according to the SQL Agent.

Maybe it's just this particular error that doesn't get trapped. Does the backup file get created? Does the error happen during a file write or something like that?

gaffer

I've done some testing and here's the results:
Test 1: Backing up a database that doesn't exist
Return Code = 1, SQL job fails
Test 2: Syntax error in .CMD file
Return Code = 0, Backup not run, SQL job completed with success
Test 3 - backup to existing file with no INIT
ReturnCode = 1, SQL job fails

This one was interesting:
Test 4 - Syntax error, run SqlBackupC.exe directly from SQL job step (CmdExec)
ReturnCode = 1, SQL job fails

It get even more interesting because I got the same error as the first one but this time the CMD returned a 1 and the SQL job failed.

Event Viewer, Application, Error message:
18210 :
BackupMedium::ReportIoError: write failure on backup device 'SQLBACKUP_182491517'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).