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


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’,
DISK = N‘C:\SQLskills\mediaset1device2.bck’
MIRROR TO DISK = N‘C:\SQLskills\mediaset2device1.bck’,
DISK = N‘C:\SQLskills\mediaset2device2.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 339,918,848 bytes – again, double the size of the non-mirrored backup in case #3. The on-disk size of each file is 81MB, as each file is one half of a copy of the backup.


Restoring


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.