通过

Can you mix-n-match backup devices?

  • 项目

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?

Comments

  • Anonymous
    August 02, 2007
    Congratulation Paul & Kimberly. and may the "force" be with you two for the rest of the journey until SQL2200 and beyond :-) Thanks for sharing with us and you bet that I will follow you on your new blog until the day I win a big jackpot! :-)  SANH