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.

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.

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

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.

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

• BACKUP (Transact-SQL)
  

To restore from a mirrored backup

• RESTORE (Transact-SQL)
  

Concepts

Other Resources

Help and Information