Ensure Robocopy after backup

Question

Ensure Robocopy after backup

Simon-220850

SSCrazy

Points: 2789
More actions
October 10, 2005 at 5:26 am

#110427

Hi,
Our current backup procedure is using a set of maintenance plans set up as a suite of jobs. However we would also like to set-up a job to robocopy the backup files to another server. I would seem to have 2 options
I could use previous jobs history to get the backup time to determine what time OI schedule the robocopy job for, although the backup time on the odd occasion takes a little longer than normal, so it can't be guaranteed that the robocopy job starts after the backup job is finished
I could set up a step within the backup job itself which would guarantee that the robocopy only runs after the backup step is finished. However SQL warns against adding steps to maintenance plan jobs
What do others tend towards ?
Cheers

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Tony Bater SSCrazy Points: 2150 More actions · Answer 1

You could schedule a new job which starts after the expected (usual) finish time of the maintenance tasks. The new job could have two steps:

1. A step which checks that the maintenance task has finished. Either a SQL task which checks the sysjobhistory table to look for the appropriate step finish time, or an xp_cmdshell task that tries to open the file you want to back up - if it is still being written this would fail. You set the 'on failure' step to repeat this task, and the 'on success' to go to the next

2. Your robocopy step. Remember with robocopy to change the 'Process exit code of a successful command' from the default of 0 to 1. Robocopy's exit code reflects the number of files copied, and a code of 0 means no files copied. Thus if it returns a 1, SQL Agent would normally intepret this as a failure! This bugged me for a long time until I realized what was happening.

Tony

Stacey SSCommitted Points: 1766 More actions · Answer 2

I also have to copy the backups to other servers. The way I handled it was first to create my normal backups with expiring backups.

Then I scheduled the Robocopy with mirroring to copy everything to my other servers. The mirroring made sure that everything on all the servers were the same, even when an old backup was removed. Not the greatest system but it simple and effective.

Stacey W. A. Gregerson

Simon-220850 SSCrazy Points: 2789 More actions · Answer 3

Hi Tony,

Thanks for that. Yes I was aware of the Robocopy Return Code Nasty (the first time I set up a robocopy job and spent many minutes scratching head). Checking the job history table certainly seems a viable option to ensure that the robocopy job runs when it is supposed to

Cheers

Simon

kmil Grasshopper Points: 15 More actions · Answer 4

I'm still having the issue where the Robocopy step is failing and as a result causing the SQL job to fail. I have changed the return code from the default of 0 to 1. I usually have a total of 5 .BAK files that copy over to another server. Some said the Robocopy return code is based on the number of files copied...so should I set my return code higher and if so, will my SQL job fail. :unsure:

Any assistance would be greatly appreciated.

Thanks in Advance!

KMil

Simon-220850 SSCrazy Points: 2789 More actions · Answer 5

Hi Kmil, which version of Robocopy are you using - the return code varies with the version - if you are using XP010 which is the version in the Windows 2003 toolkit then the return codes are as follows. (from the robocopy.doc documentation)

16Serious error. Robocopy did not copy any files. This is either a usage error or an error due to insufficient access privileges on the source or destination directories.

8Some files or directories could not be copied (copy errors occurred and the retry limit was exceeded). Check these errors further.

4Some Mismatched files or directories were detected. Examine the output log. Housekeeping is probably necessary.

2Some Extra files or directories were detected. Examine the output log. Some housekeeping may be needed.

1One or more files were copied successfully (that is, new files have arrived).

0No errors occurred, and no copying was done. The source and destination directory trees are completely synchronized.

tsquillario Old Hand Points: 316 More actions · Answer 6

What solution did any of you end up using for the robocopy return code issue?

If you just change it from 0 to 1 then this still reports a failure when say a code of 3 is returned.

Any ideas?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I setup robocopy using Task Scheduler on our Windows Server 2008 systems. Set it up to start when the system started and to move files to a separate server. Task runs continuously and i don't have to worry about dealing with the return codes.

tsquillario Old Hand Points: 316 More actions · Answer 8

I'd like to keep everything contained and visible within SQL. Since others might have to maintain this SQL Server someday I'm thinking that a scheduled task might be overlooked.

These are some of my options to execute robocopy:

- Use SQL Agent to execute a batch file, capture and output the correct return code

- Use SQL Agent (CmdExec) directly and let the job step fail

- Enable xp_cmdshell and use a Stored Procedure

- Use Task Scheduler

None of these are the solution I'm looking for, lol. I guess I just need to pick one! I would like it to be simple and not rely on other files on the system.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

By having it run as a continuously running Task at the OS level, I know that if I run an ad hoc or on demand backup, the file will be moved off the server without any additional work on my part.

The key is to have things like this documented.

tsquillario Old Hand Points: 316 More actions · Answer 10

How often do you have it running? Every 5 min or so...

GTR SSCrazy Eights Points: 8007 More actions · Answer 11

Simon-220850 (10/10/2005)
Hi,
Our current backup procedureis using a set of maintenance plans set up as a suite of jobs. However we would also like to set-up ajob to robocopy the backup files to another server. I would seem to have 2 options
I could use previous jobs history to get the backup time to determinewhat time OI schedule the robocopy job for, although the backup time on the odd occasion takes a little longer than normal, soit can't beguaranteed that the robocopy job starts <STRONG>after</STRONG> the backup job is finished
I could set up a step within the backup job itself which would guarantee that the robocopy only runs after the backup step is finished. However SQL warns against adding steps to maintenance plan jobs
What do others tend towards ?
Cheers

Can you please tell what are you trying to achieve using Robocopy? We may have alternate for Robocopy, we were using Robocoby for copying files across environments we have an alternate now.

EnjoY!

tsquillario Old Hand Points: 316 More actions · Answer 12

If you don't mind, what is the alternative you guys are using?

The post you quoted was 5 yrs old...