In some ways, this post outlines a bit of what I’d call a poor man’s approach to getting copies of your backups off-box and to a secondary (or tertiary/etc.) location. And that’s because there are lots of great ways to do this in most cases, ranging from using the MIRROR TO clause (Enterprise Edition ONLY), to similar COPY TO clauses added by most 3rd party SQL Server backup utilities, along with using Windows DFS or 3rd party file backup agents and such.
Setting the Stage – The Need for Multiple Copies of Backups
The short version of what I’m trying to get at though, is that the absolute worst thing you could ever do is keep your backups and data on the same box—so you need some way to keep backups (or copies of your backups) off-box or in a secondary/redundant location. For many small to medium businesses (without a lot of existing infrastructure or other supporting 3rd party backup tools and options), this might, then, mean that RoboCopy could be a viable way to keep copies of your backups synchronized off-box. (And, for the record, I personally prefer to execute/store all backups (FULL+DIFF / T-Log) on-box and try to keep 2-3 days' worth of those backups on-hand as they'll be faster and easier to restore from in the case of a simple disaster; but since that’d keep data and backups on the same hardware I also always make sure to either mirror or copy backups off-box—and/or off-site—as well.)
The problem, however, with using something like RoboCopy is that you might be tempted to use it via a .bat file or PowerShell script somewhere that’s fired off by the Windows Task Scheduler—something I think would be a bad move because that can (and will) fail silently without you knowing. Again, I always advocate that DBAs should be regularly checking backups and actually restoring them (from multiple sources/locations) to make sure they're working and that they're viable and restorable from all locations (i.e., on-box, off-box, and off-site) but that doesn't mean I’d advocate setting up a solution where the change to something like a machine/host name, permissions, IP addresses or other issues could cause copies of backups to fail silently—and that's exactly what would happen if you don’t build some sort of alerting into place with your .bat/.ps1 files. That, or you could just fire off those .bat/.ps1 files from the SQL Server Agent—and set up the job to run these synchronization details to send an email alert if it fails—which is what I'll outline below.
Ingredients
To set a SQL Server Agent Job that'll copy your backups from one location (i.e., your local backup location) to another/backup location, you’ll need a couple of things:
- Regular Backups (FULL/DIFF/TLOG)—which are outside the scope of this post (i.e., it’s assumed you’ve already got those set up and working).
- A file share or storage location where your backup copies will be going. (As well as sufficient permissions and access to those files for your job—more on that below.)
- A .bat file where you’ve specified RoboCopy commands. (You can also use PowerShell if you’d prefer—but I won’t be documenting that approach.)
- A bit of logic to hack how RoboCopy stupidly reports its exit-codes.
- Credentials and a Proxy to address the security aspects of having your SQL Server Agent fire off a job that reaches ‘off box’ and over the network to another ‘box’ somewhere. (Though, if your SQL Server Agent’s service account is set up as a Domain account then you can just give that account permissions on the share/folders where your copies are being ‘dumped’ and won’t need credentials + a proxy).
- A SQL Server Agent Job to run your .bat (or .ps1) file/script—along with a regular schedule and an accompanying Operator to alert and notify if/when the job fails. (To set all of this up, you’ll want to configure database mail for SQL Server and set up an operator as well—along with ensure that your SQL Server Agent can correctly ‘talk’ to database mail (as outlined in the previous link).)
Step-by-Step Instructions
Once you’ve gotten everything above assembled and configured, it's time to get started. To start, you’ll want to create a .bat (or .ps1) file that basically just tells RoboCopy to mirror or replicate everything in location A over to location B. That's pretty trivial given that RoboCopy was specifically designed for such a task—and especially given that it comes with options for what to do if a copy-operation fails (i.e., retry options).
Personally, I usually like to set up different folders for backups from different TYPEs of databases (i.e., at least a folder for system databases, and a folder for user databases—but sometimes I might make a critical folder for mission-critical databases and even a ‘pigpile-type’ folder for 'crud' databases—all so that I can set different file retention times for backups—by folder), so, with that, I'll actually create two .bat files in the following example—one for system databases, the other for user databases.
System Databases.bat
robocopy "D:\SQLBackups\System" "\\Storage\SQL Backups\System" /e /r:3 /w:15 /np
REM: suppress errors of anything < 24 because of robocopy craziness:
SET/A errlev="%ERRORLEVEL% & 24"
exit/B %errlev%
User Databases.bat
robocopy "D:\SQLBackups\System" "\\Storage\SQL Backups\System" /e /r:3 /w:15 /np
REM: suppress errors of anything < 24 because of robocopy craziness:
SET/A errlev="%ERRORLEVEL% & 24"
exit/B %errlev%
As you can see, both of those files (well, the contents of those files) are virtually identical. The only deviations are the paths to the different folders. Typically, I’ll drop these files into something like a C:\Scripts folder. Each script also specifies the following switches:
/e – to force RoboCopy to copy sub-directories
/r:3 – to retry failed copies 3 times
/w:15 – to wait 15 seconds between those retries
/np – ‘light’ logging (or what I assume stands for no progress—just to keep things tidier
You can obviously mix/match these settings as best makes sense to you.
Otherwise, note the logic below the actual calls into robocopy.exe—where we take the output from robocopy itself, use a logical AND operator against it, and then spit out the resultant value. That big of logic—which I was only too happy to find documented (and steal) from this excellent post, addresses the fact that robocopy (as great as it is) doesn’t honor the de-facto notion that non-zero return codes are seen as failures. Granted, robocopy is just trying to be a bit more verbose with its exit codes. But that’s a huge hassle when a) most command-line operations expect a zero as ‘success’ and anything other than zero as non-success and b) when SQL Server Agent Jobs that fire against the Operating System (i.e., CmdExec jobs/job-steps) can only specify a SINGLE error code/outcome to treat as success (because robocopy will be successful even when it returns a 0, 01, a 02, a 04, or even a 08). Accordingly, the AND-ing logic above just swallows those and causes robocopy to spit out a 0 instead of being verbose—so you’ll want to make sure to include that (otherwise, you’ll get alerts for job failure even when the files copy correctly and the job, logically, has not failed).
Once you’ve created the .bat files, you’ll need to create a SQL Server Agent Job to run/execute them. I typically have these jobs run about every 5 minutes (i.e., less than regular/typical 10-minute apart T-log backup jobs)—as this makes sure to keep files cleanly and regularly synchronized between the local box and the off-box backup location. (Overhead is also typically VERY light. And even if it wasn’t, I’d much prefer some CPU/IO overhead than lost/missing data in almost all situations.)
To create a job, give it a descriptive name and make sure to assign ownership to sa. Then, for each .bat file that you end up with, create/add a new Job Step more or less as shown below—where you’ll be configuring the job step to run as an Operating System command and then specifying the location of the .bat file to execute:
(Note too that I’ve also highlighted where SQL Server lets you specify the (single) exit code you can specify to identify a successful outcome.)
You might also need to specify the name of a proxy in the Run as section of this job—if you’re not using domain accounts, if you’re in a work group, etc., but that's outlined in one of the links above.
Otherwise, make sure to set/configure a schedule for the job and then also make sure to set the job to send an alert when/if the job fails, and you'll be much better taken care of than had you simply created a .bat file and had the Windows Task Scheduler kick off your job regularly (as it lacks any built-in option for notification).
