Search Engine Q&A #15: Mirrored backups


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.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.