Our IT manager at work called me with a question recently about backing up SQL Server to multiple locations via a single backup command. We have Standard edition so it turns out my answer was accurate when I told him I didn’t think it was possible, but I later found out this is a feature in Enterprise edition.

A basic database backup statement to one location looks like this, and we can use this in any edition of SQL Server.

1
2

BACKUP DATABASE MyDatabase
TO DISK = N'd:\backup\MyDatabase.bak';

If we have Enterprise edition and we want to backup to a couple locations at the same time we can use a statement like the following using MIRROR. The FORMAT option is required when using MIRROR.

1
2
3
4

BACKUP DATABASE MyDatabase
TO DISK = N'd:\backup\MyDatabase.bak'
MIRROR TO DISK = N'y:\backup\MyDatabase.bak'
WITH FORMAT;

It is possible to specify up to eight backup locations at the same time. One caveat is that if any of the backup locations is not available the whole backup job will fail.

I thought this would be great because I could backup to disk and to url at the same time, but another caveat is that you can’t use different destination media types together. That does make the feature a little less useful than I wish it could be.

This feature can also be applied to differential backups and transaction log backups.

1
2
3
4

BACKUP DATABASE MyDatabase
TO DISK = N'd:\backup\MyDatabase.bak'
MIRROR TO DISK = N'y:\backup\MyDatabase.bak'
WITH FORMAT, DIFFERENTIAL;

1
2
3
4

BACKUP LOG MyDatabase
TO DISK = N'd:\backup\MyDatabase.trn'
MIRROR TO DISK = N'y:\backup\MyDatabase.trn'
WITH FORMAT;

It is also possible to combine striped backups with mirror backups.

1
2
3
4
5
6
7
8
9

BACKUP DATABASE MyDatabase
TO DISK = 'd:\backup\MyDatabase_1.bak',
   DISK = 'd:\backup\MyDatabase_2.bak',
   DISK = 'd:\backup\MyDatabase_3.bak'
MIRROR
TO DISK = 'y:\backup\MyDatabase_1.bak',
   DISK = 'y:\backup\MyDatabase_2.bak',
   DISK = 'y:\backup\MyDatabase_3.bak'
WITH FORMAT;