Introduction

This article covers how to use Microsoft's ROBOCOPY program with the SQL Server Agent in order to copy database backup files to a disaster recovery server.

We have several DR implementations, which vary depending on the needs of the business unit affected. In a couple of cases, the recovery time objective is quite long (i.e. it is not imperative that the service be restored immediately). For these systems, a simple copy of the database backups suffices; there is no need to have a warm standby (e.g. through use of log shipping).

About ROBOCOPY

ROBOCOPY is a Microsoft product which ships with Windows 2008 and later or can be downloaded as part of the Windows 2000 or Windows 2003 Resource Kits (more information can be found at http://en.wikipedia.org/wiki/Robocopy).

A problem using ROBOCOPY with the SQL Server Agent is the return codes ROBOCOPY uses. A return code of 0, 1, 2, 3, 5, 6, or 7 indicates different types of successful execution (e.g. no file needed to be copied, some files were copied, all files were copied. For more information on ROBOCOPY return codes, see http://support.microsoft.com/kb/954404/en-us).

However, if SQL Server Agent receives a return code of anything other than 0, (zero), it marks the job as failed.

The Solution

An article by Rob Volk on SQLTeam.com turned out to be incredibly useful: http://weblogs.sqlteam.com/robv/archive/2010/02/17/61106.aspx (there are other articles & forums out there with similar information, but this was the first one I came across).

Rob's article covers how to intercept the codes returned by ROBOCOPY and filter out the successes that would be interpreted as failures by SQL Server Agent.

ROBOCOPY has a switch /MIR, which MIRrors the contents of one folder to another - removing deleted files as well as adding new files. I also use the /R and /W switches to control the number of retries and the wait time inbetween (the defaults are 1 million and 30 seconds respectively - so if there are problems accessing a file, ROBOCOPY could be running for a while!).

So to copy a single folder to another server, you would perform the following steps:

1. Create a share on the remote server, making sure to assign modify rights to only the SQL Server Agent service account (or SQL Server Agent Proxy Account).
2. Paste the following code into a text file that is in a folder that the SQL Server Agent service account, (or SQL Server Agent Proxy Account), has read/execute rights to:

@echo off
REM Script to copy backups from ServerA to ServerB
REM Need to manage robocopy exit codes as it can report an exit code
REM  of something greater than 0 but still be successful, whereas
REM  SQL Server interprets anything with an exit code other than 0
REM  as a failure.
REM See http://support.microsoft.com/kb/954404 for robocopy exit codes.
REM See http://weblogs.sqlteam.com/robv/archive/2010/02/17/61106.aspx for workaround
REM Copy user database backups
robocopy "C:\Program Files\Microsoft SQL Server\MSSQL10.INST1\MSSQL\Backup\UserDBs" \\RemoteServer\UserDBs$ /MIR /R:1 /W:5
REM Use bitwise & to eliminate non-errors & return 0 for success
SET /A ERRLEV = "%ERRORLEVEL% & 24"
EXIT /B %ERRLEV%

3. Change the extension of the text file created above to .cmd.
4. Create a SQL Server Agent job with a job step of type Operating system (CmdExec) to run the command file created above (See Figure 1 below).Figure 1.
5. Set a schedule for the job to run. If you are only doing full backups, set this to run some time after the regular backup completes. If you are doing log backups, you can either schedule this to run after each log backup completes, certain times throughout the day, or once a day, (preferably after the full backup completes).

We have separate subfolders for user databases and system databases in the main backup folder, along with a few other folders for other purposes. We don't want to copy the other folders, just the user and system database backup folders. Instead of creating and running two batch files, we use the steps above, but use the following code in the command file (NB - we also use separate target shares as well):

@echo off
REM Script to copy backups from ServerA to ServerB
REM Need to manage robocopy exit codes as it can report an exit code
REM  of something greater than 0 but still be successful, whereas
REM  SQL Server interprets anything with an exit code other than 0
REM  as a failure.
REM See http://support.microsoft.com/kb/954404 for robocopy exit codes.
REM See http://weblogs.sqlteam.com/robv/archive/2010/02/17/61106.aspx for workaround
REM Copy user database backups
robocopy "C:\Program Files\Microsoft SQL Server\MSSQL10.INST1\MSSQL\Backup\UserDBs" \\RemoteServer\UserDBs$ /MIR /R:1 /W:5
REM Use bitwise & to eliminate non-errors & return 0 for success
SET /A ERRLEV1 = "%ERRORLEVEL% & 24"
REM Copy system database backups
robocopy "C:\Program Files\Microsoft SQL Server\MSSQL10.INST1\MSSQL\Backup\SysDBs" \\RemoteServer\SysDBs$ /MIR /R:1 /W:5
REM Use bitwise & to eliminate non-errors & return 0 for success
SET /A ERRLEV2 = "%ERRORLEVEL% & 24"
REM Check both ERRLEV variables above & eliminate non-errors for final return code
REM Probably don't need to & 24 again, but won't hurt.
SET /A ERRLEV = "(%ERRLEV1% & %ERRLEV2%) & 24"
EXIT /B %ERRLEV%

Conclusion

So that's a quick & dirty way to have your SQL Server backups synchronised to a remote, or even another local, server. The other server doesn't even need to have SQL Server installed. It's not a full disaster recovery solution by any means, but it is a quick and easy way to ensure you have some offsite, or just off-server, backups for those non-critical servers (or any server, in lieu of having anything else). It could also provide a faster recovery option, instead of restoring from tape.

Of course, bigger database backups will take longer to copy, and if you have slow network links or old hardware, this may not be an ideal option. Take care, and perhaps do some speed tests first.