Mirrored Backups: a useful feature?
One of the features found in the Enterprise Edition of SQL Server is the ability to take mirrored backups. Basically, taking a mirrored backup means creating additional copies of the backup media (up to three) using a single BACKUP command, eliminating the need to perform the copies with copy or robocopy.
The idea behind is that you can backup to multiple locations and increase the protection level by having additional copies of the backup set. In case one of the copies gets lost or corrupted, you can use the mirrored copy to perform a restore.
BACKUP DATABASE [AdventureWorks2008R2] TO DISK = 'C:\backup\AdventureWorks2008R2.bak' MIRROR TO DISK = 'H:\backup\AdventureWorks2008R2.bak' WITH FORMAT; GO
Another possible scenario for a mirrored backup is deferred tape migration: you can backup to a local disk and mirror to a shared folder on a file server. That way you could have a local copy of the backup set and restore it in case of need and let the mirrored copy migrate to tape when the disk backup software processes the file server’s disks.
Mirrored backup sets can be combined with striped backups, given that all the mirror copies contain the same number of stripes:
BACKUP DATABASE [AdventureWorks2008R2] TO DISK = 'C:\backup\AdventureWorks2008R2_1.bak', DISK = 'C:\backup\AdventureWorks2008R2_2.bak', DISK = 'C:\backup\AdventureWorks2008R2_3.bak' MIRROR TO DISK = 'H:\AdventureWorks2008R2_1.bak', DISK = 'H:\AdventureWorks2008R2_2.bak', DISK = 'H:\AdventureWorks2008R2_3.bak' WITH FORMAT; GO
When restoring from a striped + mirrored backup set, you can mix the files from one media with the files from another media, as each mirrored copy is an exact copy of the main backup set.
RESTORE DATABASE [AW_Restore] FROM DISK = N'C:\backup\AdventureWorks2008R2_1.bak', -- main media DISK = N'H:\AdventureWorks2008R2_2.bak', -- mirror media DISK = N'H:\AdventureWorks2008R2_3.bak' -- mirror media WITH FILE = 1, MOVE N'AdventureWorks2008R2_Data' TO N'C:\DATA\AW_Restore.mdf', MOVE N'AdventureWorks2008R2_Log' TO N'C:\DATA\AW_Restore_1.ldf', MOVE N'FileStreamDocuments2008R2' TO N'C:\DATA\AW_Restore_2.Documents2008R2', NOUNLOAD, STATS = 10; GO
Looks like a handy feature! However, some limitations apply:
- If striped, the mirror must contain the same number of stripes.
Looks sensible: each mirror copy is an exact copy of the main backup set, which would be impossible with a different number of devices.
- Must be used with FORMAT option.
No append supported: the destination device must be overwritten.
- Destination media must be of the same type.
You cannot use disk and tape together. I can understand the reason for this restriction, but, actually, it makes this feature much less useful than it could be.
- Fails the backup if ANY of the mirrored copies fails.
This is the main pain point: creating multiple copies of the same backup set can end up reducing the protection level, because the whole backup process fails when at least one of the destination media is unavailable or faulty.
Does this mean that the ability to take mirrored backups is a useless feature?
Well, it highly depends on your point of view and what matters to you most. I would prefer having at least one copy of the database backup available rather than no backup at all.
Keeping in mind that:
- the same exact result can be accomplished using copy, xcopy or robocopy
- non-local copies are much more likely to fail rather than local copies
- taking multiple local copies is quite pointless
- Enterprise Edition costs a lot of money
- There’s no GUI in SSMS backup dialog, nor in Maintenance Plans
…I think I could live without this feature. At least, this is not one of the countless reasons why I would prefer Enterprise over cheaper editions.
Posted on December 29, 2011, in SQL Server and tagged backup, backup database, backup set, mirror copies, SQL, SQL Server. Bookmark the permalink. 1 Comment.
Very good, thanks for all.