Using Mirrored Backup Media Sets
Note
Mirrored backup media sets are supported only in SQL Server 2005 Enterprise Edition.
Microsoft SQL Server 2005 introduces mirroring of backup media sets. Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions. These malfunctions are very serious because backups are the last line of defense against data loss. As databases grow, the probability increases that a failure of a backup device or media will make a backup nonrestorable. Mirroring backup media increases the reliability of backups by providing redundancy.
Note
For information about media sets in general, see Media Sets, Media Families, and Backup Sets.
Introduction to Mirrored Media Sets
Media mirroring is a property of the media set. A mirrored media set consists of multiple copies (mirrors) of the media set. A media set contains one or more media families, each of which corresponds to a backup device. For example, if the TO clause of a BACKUP DATABASE statement lists three devices, BACKUP spreads the data among three media families, one per device. The number of media families and mirrors is defined when the media set is created (by a BACKUP DATABASE statement that specifies WITH FORMAT).
Note
For an introduction to media families and media sets, see Media Sets, Media Families, and Backup Sets.
A mirrored media set possesses from two to four mirrors. Each mirror contains all the media families in the media set. The mirrors require the same number of devices, one per media family. Each mirror requires a separate backup device for each media family. For example, a mirrored media set that consists of four media families with three mirrors requires twelve backup devices. All of these devices must be equivalent. For example, tape drives that have the same model number from the same manufacturer.
The following illustration shows an example of a mirrored media set that consists of two media families with two mirrors. Each media family contains three media volumes, which are backed up one time per mirror.
.gif "Mirrored media set: two families with two mirrors")
Corresponding volumes on the mirrors have identical contents. This makes them interchangeable at restore time. For example, in the previous illustration, the third volume of tape2 is interchangeable with the third volume of tape0.
Specifying a Mirrored Media Set
To specify a mirrored media set in a BACKUP statement, specify the first mirror in the TO clause. Then, specify each mirror in its own MIRROR TO clause. The TO and MIRROR TO clauses must specify the same number and type of backup devices.
The following example creates the mirrored media set illustrated in the previous illustration and backs up the AdventureWorks database to both mirrors.
BACKUP DATABASE AdventureWorks
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1'
MIRROR TO TAPE = '\\.\tape2', TAPE = '\\.\tape3'
WITH
FORMAT,
MEDIANAME = 'AdventureWorksSet1';
GO
Hardware Requirements for Backup Mirrors
Mirroring applies both to disk and tape (disks do not support continuation tapes). As in earlier versions of SQL Server, all backup devices for a single backup or restore operation must be of the same type, disk or tape.
Within these broader classes, you must use similar devices that have the same properties. Insufficiently similar devices generate an error message (3212). To avoid the risk of a device mismatch, use devices that are equivalent, such as, only drives with the same model number from the same manufacturer.
How Media Mirroring Works
Backup and restore operations impose different requirements on whether all the mirrors must be present. For a backup operation to write (that is, to create or extend) a mirrored media set, all the mirrors must be present. In contrast, a restore operation can read the media for only one mirror per media family at a time. However, in the presence of errors, having the other mirrors enables some restore problems to be resolved quickly. This is because RESTORE and RESTORE VERIFYONLY support substitution of damaged media with the corresponding backup-media volume from another mirror. Be aware that you can restore from fewer devices than families, but each media family is processed only one time.
The SQL Server Database Engine guarantees that the mirrored media have identical contents by synchronizing writes to the devices. When any one of the mirrors fills, all the mirrors are spanned at one time.
Note
A mirrored media set cannot be implicitly broken (split) by removing a mirror. If any tape or disk in a mirror is damaged or reformatted, the mirror is no longer usable for additional backups. If at least one full mirror remains intact, the media set can be read. If every mirror loses a given media family, the media set is useless.
To back up to mirrored backup devices
To restore from a mirrored backup
See Also
Concepts
Detecting and Coping with Media Errors
RAID Levels and SQL Server
Responding to SQL Server Restore Errors Caused by Damaged Backups
Other Resources
RESTORE VERIFYONLY (Transact-SQL)
Using Backup Media