I was asked this question yesterday and didn't know the answer so thought it would be good for a quick post. Can you use devices from your main and mirrored backup media sets together to perform a restore?
The code below creates a single-device backup and then examines it.
BACKUP
DATABASE AdventureWorks
TO
DISK = N'c:\mediaset1device1.bck'
WITH
FORMAT, STATS;
GO
RESTORE
HEADERONLY FROM DISK=N'c:\mediaset1device1.bck';
GO
The BackupSize in the output is 172,044,800 bytes and the on-disk size of the file is 164MB. Now let's use two devices and see what happens.
BACKUP
DATABASE AdventureWorks
TO
DISK=N'c:\mediaset1device1.bck', DISK=N'c:\mediaset1device2.bck'
MIRROR
TO DISK=N'c:\mediaset2device1.bck', DISK=N'c:\mediaset2device2.bck'
WITH
FORMAT, STATS;
GO
RESTORE
HEADERONLY FROM DISK=N'c:\mediaset1device1.bck';
GO
The
BackupSize is now 344,113,152 bytes (roughly double what it was previously because we've mirrored the whole backup) and the on-disk size of each file is 82MB (half of what it was for a single-device). This proves that the backup has been split over the two devices. 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:\mediaset1device1.bck', DISK=N'c:\mediaset2device2.bck'
WITH
REPLACE, STATS;
GO
And it works fine - excellent! That's the whole point of having mirrored backups.
The second part of the question was about whether 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.
Next up - can it hurt to have thousands of filegroups?
