Many IT shops need copies of a backup saved to multiple locations. Sometimes it’s a disaster recovery thing. After all, it is a very good idea to have at least one “quick access” backup, and one offsite backup.
Sometimes multiple backups have more to do with testing and quality assurance. For example, we frequently restore production databases to test servers.
So what’s the best way to back up your databases to multiple locations?
Option 1: Mirror the Backup
One option is to perform a mirrored backup, which is a native feature of SQL Server backups. In Minion backup, mirroring your backup is a simple process:
- Enable mirrored backups in the Minion.BackupSettings table by setting Mirror = 1.
- Configure a backup mirror path in Minion.BackupSettingsPath, being sure to set isMirror = 1. (It’s very important that you have a primary backup path, in addition to the mirror backup path.)
The downside to mirroring your backups is that two backup locations means twice the chance of backup failure! If something goes awry with the mirror backup, the primary backup will fail, too.
Another downside is that mirrored backups are only available on SQL Server Enterprise.
For more information on mirrored backups, use the command
EXEC Minion.HELP ‘backup’, ‘How to: Set up mirror backups’;
in Minion Backup, or see that section in the help document posted on MinionWare.net/backup/
Option 2: Copy the Backup
With Minion Backup, you can choose to copy your backup files to one or more locations as part of your backup routine. This feature is very flexible:
- perform the file move after the batch, or immediately after the backup completes
- choose which utility to use to perform the copy: COPY, MOVE, XCOPY, ROBOCOPY, or ESEUTIL
- define any number of copy paths
To configure copy operations for backup files:
- Configure when the copy will happen in the Minion.BackupSettings table, using FileAction = ‘COPY’ and FileActionTime = ‘AfterBatch’ (or, AfterBackup).
- Insert one row per copy path into the Minion.BackupSettingsPath table.
For example, to copy all backups from a server to two new locations, we first enable COPY / AfterBatch:
UPDATE Minion.BackupSettings
SET FileAction = ‘COPY’ ,
FileActionTime = ‘AfterBatch’;
Second, set up the two backup copy paths:
INSERT INTO Minion.BackupSettingsPath (DBName, IsMirror, BackupType,
BackupLocType, BackupDrive, BackupPath, RetHrs, FileActionMethod,
FileActionMethodFlags, PathOrder, IsActive, Comment )
SELECT ‘MinionDefault’ AS DBName
, 0 AS IsMirror
, ‘COPY’ AS BackupType
, ‘NAS’ AS BackupLocType
, ‘\\QWERTY\’ AS BackupDrive
, ‘SQLBackup\’ AS BackupPath
, ‘168 ‘ AS RetHrs
, ‘COPY’ AS FileActionMethod
, NULL AS FileActionMethodFlags
, 20 AS PathOrder
, 1 AS IsActive
, ‘Copy path 1’ AS Comment
UNION
SELECT ‘MinionDefault’ AS DBName
, 0 AS IsMirror
, ‘COPY’ AS BackupType
, ‘NAS’ AS BackupLocType
, ‘\\ASDF\’ AS BackupDrive
, ‘SQLBackupCopies\’ AS BackupPath
, ’72’ AS RetHrs
, ‘COPY’ AS FileActionMethod
, NULL AS FileActionMethodFlags
, 10 AS PathOrder
, 1 AS IsActive
, ‘Copy path 2’ AS Comment;
From here on out, each batch of backups will run. Then after the backups, MB will copy the files to the \\QUERTY location, and then to the \\ASDF location. (Note that the order is determined by the PathOrder field.)
For more information on copying backup files, use the command
EXEC Minion.HELP ‘backup’, ‘How to: Copy files after backup (single and multiple locations)’;
in Minion Backup, or see that section in the help document posted on MinionWare.net/backup/
Download the FREE Minion maintenance modules:
- Minion Backup: Free SQL Server Backup utility with world-class enterprise features and full lifecycle management.
- Minion Reindex: Free SQL Server Reindex utility with unmatched configurability.
- Minion CheckDB: Free SQL Server CHECKDB that solves all of your biggest CHECKDB problems.