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
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
RESTORE HEADERONLY FROM DISK = N‘C:\SQLskills\mediaset1device1.bck’;
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\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:
DATABASE AdventureWorks
RESTORE
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.
2 thoughts on “Search Engine Q&A #15: Mirrored backups”
Hi,
It’s clear now.
Thank you again for help us.