SQL Server Agent Job Not Detecting Robocopy Command Failures

Data and Databases
1

I’m running a SQL Server Agent job that uses robocopy to transfer files to a backup location. The job executes a batch script through CmdExec but I’m having trouble with error detection.

Even when robocopy encounters actual errors like invalid source paths, the SQL job still shows as successful. I tested this by pointing to a non-existent drive and the job completion status remained positive.

The problem seems to be that SQL Server only checks if robocopy launched successfully, not whether the actual file operations worked.

Here’s my current batch script:

xcopy "E:\source\" "F:\destination"
SET /A exitcode="%ERRORLEVEL & 24%"
EXIT /B %exitcode%

How can I make the SQL Server job properly report when file copy operations actually fail?

2

i think u should definitely switch to robocopy since it provides better error reporting. remember, robocopy’s exit codes range from 0-7 for success, and anything above 7 indicates an error. this way, u can directly check the exit code without any masking!

3

Wait, you’re using xcopy in the script but mentioned robocopy in the question - is that intentional? Also curious why you’re masking with & 24 specifically? Have you tried logging robocopy output to a file first to see what error codes it’s actually returning?

4

Your batch script is masking the actual error codes before SQL Server can see them. That & 24 operation filters out important error info that SQL Server needs to know if the job failed. Don’t mask the codes - let the full error code pass through to SQL Server and set up the job step to handle them properly. In the SQL Server Agent job step properties, go to the “Advanced” tab and specify which exit codes mean success vs failure. For robocopy, codes 0-3 usually mean success (with different levels of file activity), while 8+ are real errors that should fail the job.