This post is based on one from my old MSDN blog but the topic has come up a few times in recent days so I want to revamp it and re-post.

There are two things that confuse people about mirrored backups - can you mix-n-match backup devices from the mirrors, and what exactly do the various sizes mean?

1) Single-device backup, no mirror

The code below creates a single-device backup with no mirror, and then examines it.

BACKUP DATABASE AdventureWorks TO
DISK = N'C:\SQLskills\mediaset1device1.bck'
WITH FORMAT, STATS;
GO

RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\mediaset1device1.bck';
GO

The BackupSize in the HEADERONLY output is 168,899,072 bytes and the on-disk size of the file mediaset1device1.bck is 161MB.

2) Single-device backup, mirrored

The code below creates a single-device backup with a mirror, and then examines it.

BACKUP DATABASE AdventureWorks TO
DISK = N'C:\SQLskills\mediaset1device1.bck'
MIRROR TO DISK = N'C:\SQLskills\mediaset2device1.bck'
WITH FORMAT, STATS;
GO

RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\mediaset1device1.bck';
RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\mediaset2device1.bck';
GO

The BackupSize in the HEADERONLY output of both files is 337,798,144 bytes. This is double the size of the backup in case #1 above - and it because there are now two copies of the backup. The on-disk size of both files is 161MB, which is what we'd expect as mediaset2device1.bck is a copy of mediaset1device1.bck.

3) Two-device backup, no mirror

The code below creates a two-device backup with no mirror, and then examines it.

BACKUP DATABASE AdventureWorks TO
DISK = N'C:\SQLskills\mediaset1device1.bck',
DISK = N'C:\SQLskills\mediaset1device2.bck'
WITH FORMAT, STATS;
GO

RESTORE HEADERONLY FROM DISK = N'C:\SQLskills\mediaset1device1.bck';
GO

The BackupSize in the HEADERONLY output is 169,959,424 bytes. This is nearly exactly the same as for the single-device backup in case #1, but includes a bit more to account for the extra metadata in the second device. This time, the on-disk size of the file mediaset1device1.bck is 81MB. This is half of the on-disk size from the single-device case #1 as the backup is now split between the two files.

4) Two-device backup, mirrored

Now let's try to mix devices from the two backup media sets and see if it's possible:

RESTORE DATABASE AdventureWorks
FROM DISK = N'C:\SQLskills\mediaset1device1.bck',
DISK = N'C:\SQLskills\mediaset2device2.bck'
WITH REPLACE, STATS;
GO

And it works fine - excellent! That's the whole point of having mirrored backups.

One other question is - can backup device types can differ between media sets in the same backup. The answer to this is no - as documented in Books Online. All the backup devices involved in a single backup, regardless of whether they're part of a mirror media set or not, must be of the same type and have similar characteristics.

Hope this is useful.