Fixing Robocopy for SQL Server Jobs
- by Most Valuable Yak (Rob Volk)
Robocopy is one of, if not the, best life-saving/greatest-thing-since-sliced-bread command line utilities ever to come from Microsoft. If you're not using it already, what are you waiting for?
Of course, being a Microsoft product, it's not exactly perfect. ;) Specifically, it sets the ERRORLEVEL to a non-zero value even if the copy is successful. This causes a problem in SQL Server job steps, since non-zero ERRORLEVELs report as failed.
You can work around this by having your SQL job go to the next step on failure, but then you can't determine if there was a genuine error. Plus you still see annoying red X's in your job history.
One way I've found to avoid this is to use a batch file that runs Robocopy, and I add some commands after it (in red):
robocopy d:\backups \\BackupServer\BackupFolder *.bak
rem suppress successful robocopy exit statuses, only report genuine errors (bitmask 16 and 8 settings)set/A errlev="%ERRORLEVEL% & 24"
rem exit batch file with errorlevel so SQL job can succeed or fail appropriatelyexit/B %errlev%
(The REM statements are simply comments and don't need to be included in the batch file)
The SET command lets you use expressions when you use the /A switch. So I set an environment variable "errlev" to a bitwise AND with the ERRORLEVEL value.
Robocopy's exit codes use a bitmap/bitmask to specify its exit status. The bits for 1, 2, and 4 do not indicate any kind of failure, but 8 and 16 do. So by adding 16 + 8 to get 24, and doing a bitwise AND, I suppress any of the other bits that might be set, and allow either or both of the error bits to pass.
The next step is to use the EXIT command with the /B switch to set a new ERRORLEVEL value, using the "errlev" variable. This will now return zero (unless Robocopy had real errors) and allow your SQL job step to report success.
This technique should also work for other command-line utilities. The only issues I've found is that it requires the commands to be part of a batch file, so if you use Robocopy directly in your SQL job step you'd need to place it in a batch. If you also have multiple Robocopy calls, you'll need to place the SET/A command ONLY after the last one. You'd therefore lose any errors from previous calls, unless you use multiple "errlev" variables and AND them together. (I'll leave this as an exercise for the reader)
The SET/A syntax also permits other kinds of expressions to be calculated. You can get a full list by running "SET /?" on a command prompt.
