Robocopy File Transfer Backup using SQL Server

Question

I have a job running robocopy to copy files from the SQL server to a backup drive.

The job runs a CmdExec type job to call a batch file that runs robocopy. my issue is although the files copy over fine the error reports dont get registared I tested this by changing the source file to a bogus drive. It still reports job success. I have narrowed it down to it only sees robocopy and when it runs the tool is reports sucess. e.g. if i only type "robocopy" in to the batch file it still reports success.

Here is the code I am running to try and result in an error when a legite error has occorred:

robocopy "C:\backup\" "D:\backup" SET /A errlev="%ERRORLEVEL & 24%" EXIT /B %errlev%

Any thoughts?

Answer 1

I would take a look at: ss64's page on robocopy

Also, first time that I've seen this: RoboCopy GuI. Bet it might help you produce a working as expected command line.

Oh, an maybe I'm wrong about sql being an important part of this question. See here for a really neat guide over at SqlTeam.com that looks like it EXACTLY answers your question?

Answer 2

In your job step, check to make sure that you've selected "Quit the job reporting failure" in the "On failure action". Also, under "Process exit code of a successful command" make sure that you have 0. There are some other successful "error codes", as Maassql mentions, but I'd start with 0 until you get things working as expected, then add in the additional codes which you want to count as non-errors.

This is assuming SQL 2008. If you're on a different version, the prompts may be different.

Answer 3

I know this is an old post but

your code seems to work, but must be wrapped in a batch file, then the sql needs to call the batch file instead of robocopy directly

found the answer here

http://weblogs.sqlteam.com/robv/archive/2010/02/17/61106.aspx