In today’s digital age, data is the lifeblood of any organization. Losing critical data can have severe consequences, which is why having a robust disaster recovery plan is essential. In this article, we will explore how to use Microsoft’s ROBOCOPY program with the SQL Server Agent to copy database backup files to a disaster recovery server.

What is ROBOCOPY?

ROBOCOPY is a powerful file replication tool developed by Microsoft. It is included with Windows 2008 and later versions or can be downloaded as part of the Windows 2000 or Windows 2003 Resource Kits. ROBOCOPY allows you to mirror the contents of one folder to another, ensuring that both folders are in sync.

The Challenge with SQL Server Agent

When using ROBOCOPY with the SQL Server Agent, there is a challenge with the return codes that ROBOCOPY uses. SQL Server Agent considers any return code other than 0 as a failure, even though some return codes indicate successful execution. This can lead to false alarms and unnecessary job failures.

The Solution

Fortunately, there is a workaround for this issue. Rob Volk, a SQL Server expert, has shared a solution on SQLTeam.com. The solution involves intercepting the return codes from ROBOCOPY and filtering out the successes that would be interpreted as failures by SQL Server Agent.

Here are the steps to implement this solution:

1. Create a share on the remote server and assign modify rights to only the SQL Server Agent service account or SQL Server Agent Proxy Account.
2. Create a command file with the following code:

@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 command file to .cmd.
4. Create a SQL Server Agent job with a job step of type Operating system (CmdExec) to run the command file.
5. Schedule the job to run after the regular backup completes.

Conclusion

Using ROBOCOPY with SQL Server Agent provides a quick and easy way to synchronize your SQL Server backups to a remote or another local server. It is not a full disaster recovery solution, but it can serve as an offsite backup option for non-critical servers or as a faster recovery option instead of restoring from tape.

However, it’s important to consider factors such as network speed and hardware capabilities when implementing this solution. Larger database backups may take longer to copy, and slow network links or outdated hardware may not be ideal for this approach.

Having a reliable disaster recovery plan is crucial for any organization. By leveraging tools like ROBOCOPY and SQL Server Agent, you can ensure the safety and availability of your critical data.

Take care and consider conducting speed tests before implementing this solution.