Understanding Media Sets and Families

Microsoft® SQL Server™ introduces the concepts of a media set and media family when using multiple backup media and devices.

A media set can contain one or more backup sets and describes all of the media used by those backup sets, regardless of the number of media or backup devices involved. For example, if four tape backup devices are used when creating a database backup, and five tapes per tape backup device are used to store the backup, the media set contains 20 tapes.

A media family describes all the media used by a single backup device for a single backup set. In the example earlier, there are four media families with each set of five tapes used by each tape backup device comprising one media family.

The initial media is the first media in a media family. If the initial media becomes full during the backup operation, more media is used until the backup operation is complete. All media, except the initial media, in a media family is described as continuation media.

Note Only tape backup devices use continuation media, allowing SQL Server to continue writing the backup after the initial tape is full.

To distinguish between each physical medium used within a media family, each medium is tagged with a sequence number to specify the order in which the media were used. The initial media is tagged with 1, and second medium (the first continuation media) with 2, and so on. These sequence numbers are used when the backup set is restored to ensure that the operator restoring the backup mounts the correct media in the correct order. Additionally, media families within a media set are numbered sequentially.

When appending a backup set to a media set containing multiple media families, the sequence numbers are used to confirm that the last medium in each media family is mounted before the backup is written. If the last media in a media family is not mounted, SQL Server scans forward until the correct sequence number is found, requiring media to be changed until the last media in the family is mounted correctly.

Each SQL Server backup is stored on a media set, regardless of the number of backup devices used by the individual backup operation. Examples of media sets include:

• A single disk file.
• A single tape.
• A set of tapes written by one backup device. This set of tapes consists of a single media family (an initial media and one or more continuation media).
• A set of tapes written by four backup devices. Each set of tapes written by one backup device is the media family. Each media family contains an initial media and possibly one or more continuation media.
• A set of three disk files, used by one or more backup operations with each backup operation using three backup devices.

When using multiple backup devices:

• The entire media set created by a backup operation must be used by all subsequent backup operations. For example, if a media set was created using two tape backup devices, all subsequent backup operations involving the same media set must use two backup devices.
• It is not necessary to use the same backup devices used by the media set when restoring the backup. For example, restoring using fewer backup devices may be necessary when moving a database to another server, because the server may have fewer physical backup devices, such as only one tape device. In this situation, the initial media families are processed first. The backup device(s) then prompt for additional media families, until all of the media families have been processed.

See Also

Backup Media