Media Sets, Media Families, and Backup Sets
The backups on a set of one or more backup media compose a single media set. A media set is an ordered collection of backup media, tapes or disk files, to which one or more backup operations have written using a fixed type and number of backup devices. A given media set uses either tape drives or disk drives, but not both. For example, the backup devices associated with a media set might be three tape drives named \\.\TAPE0, \\.\TAPE1, and \\.\TAPE2. That media set contains only tapes, starting with a minimum of three tapes (one per drive). The type and number of backup devices are established when a media set is created, and they cannot be changed. However, if necessary, between backup and restore operations a given device can be replaced with a device of the same type.
A media set is created on the backup media during a backup operation by formatting the backup media. For more information, see Creating a New Media Set. After formatting, each file or tape contains a media header for the media set and is ready to receive backup content. With the header in place, the backup operation proceeds to back up the specified data to the backup media on all of the backup devices specified for the operation.
Note
Media sets can be mirrored to protect against a damaged media volume (a tape or disk file). For more information, see Using Mirrored Backup Media Sets.
Backup compression was introduced in SQL Server 2008 Enterprise. Beginning in SQL Server 2008 R2, backup compression is supported by SQL Server 2008 R2 Standard and all higher editions. Every edition of SQL Server 2008 and later can restore a compressed backup. Compressed and uncompressed backups cannot occur together in a media set. For more information, see Backup Compression (SQL Server).
Media Families
Backups created on a single nonmirrored device or a set of mirrored devices in a media set constitute a media family. The number of backup devices used for the media set determines the number of media families in a media set. For example, if a media set uses two nonmirrored backup devices, the media set contains two media families.
Note
In a mirrored media set, each media family is mirrored. For example, if six backup devices are used to format a media set, where two mirrors are used, there are three media families, each containing two equivalent copies of backup data. For more information about mirrored media sets, see Using Mirrored Backup Media Sets.
Each tape or disk in a media family is assigned a media sequence number. The media sequence number of a disk is always 1. In a tape media family, the sequence number of the initial tape is 1, the sequence number of the second tape is 2, and so forth. For more information, see Using Media Sets and Families.
The Media Header
Every volume of backup media (disk file or tape) contains a media header that is created when by the first backup operation that uses the tape (or disk). That header remains intact until the media is reformatted.
The media header contains all of the information required to identify the media (disk file or tape) and its place within the media family to which it belongs. This information includes:
The name of the media.
The media name is optionally, but we recommend consistently using media names that clearly identify your media. A media name is assigned by whoever formats the media.
The unique identification number of the media set.
The number of media families in the media set.
The sequence number of the media family containing this media.
The unique identification number for the media family.
The sequence number of this media in the media family. For a disk file, this value is always 1.
Whether the media description contains an MTF media label or a media description.
Note
All media that is used for a backup or restore operation use a standard backup format called Microsoft Tape Format (MTF). MTF allows users to specify a tape label that contains a MTF-specific description. SQL Server preserves any MTF media label written by another application but does not write MTF media labels.
The Microsoft Tape Format media label or the media description (in free-form text).
The name of the backup software that wrote the label.
The unique vendor identification number of the software vendor that formatted the media.
The date and time the label was written.
The number of mirrors in the set (1-4); 1 indicates an unmirrored device.
SQL Server 2008 can process media formatted by earlier versions of SQL Server.
Important
Media that is formatted with SQL Server 2008 cannot be interpreted by SQL Server version 7.0 and releases of SQL Server 2000 before SP4 because of changes in the media header. However, SQL Server 2000 SP4 supports the changes in media headers.
To read the media header of the media on a backup device
Backup Sets
A successful backup operation adds a single backup set to the media set. The backup set is described in terms of the media set to which the backup belongs. If the backup media consists of only one media family, that family contains the entire backup set. If the backup media consists of multiple media families, the backup set is distributed among them. On each medium, the backup set contains a header that describes the backup set.
The following example shows a Transact-SQL statement that creates a media set called MyAdvWorks_MediaSet_1 for the AdventureWorks2008R2 database using three tape drives as backup devices:
BACKUP DATABASE AdventureWorks2008R2
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1', TAPE = '\\.\tape2'
WITH
FORMAT,
MEDIANAME = 'MyAdvWorks_MediaSet_1';
If successful, this backup operation results in a new media set containing a new media header and one backup set spread across three tapes. The following figure illustrates these results:
.gif "Media header and first backup set on 3 tapes")
Typically, after a media set is created, subsequent backup operations, one after another, append their backup sets to the media set. All of the media used by a backup set make up the media set, regardless of the number of media or backup devices involved. Backup sets are sequentially numbered by their position in the media set, allowing you to specify which backup set to restore.
Every backup operation to a media set must write to the same number and type of backup devices. With multiple devices, as with the first backup set, the content of every subsequent backup set is distributed among the backup media on all of the devices. To continue the above example, a second backup operation (a differential backup) appends information to the same media set:
BACKUP DATABASE AdventureWorks2008R2
TO TAPE = '\\.\tape0', TAPE = '\\.\tape1', TAPE = '\\.\tape2'
WITH
NOINIT,
MEDIANAME = 'AdventureWorksMediaSet1',
DIFFERENTIAL;
Note
The NOINIT option is the default, but is included for clarity.
If the second backup operation succeeds, it writes a second backup set to the media set, with the following distribution of backup content:
.gif "Second backup set spread across 3 media-set tapes")
When you are restoring backups, you can use you the FILE option to specify which backups you want to use. The following example shows the use of FILE **=**backup_set_file_number clauses when restoring a full database backup of the AdventureWorks2008R2 database followed by a differential database backup on the same media set. The media set uses three backup tapes, which are on tape drives \\.\tape0, tape1, and tape2.
RESTORE DATABASE AdventureWorks2008R2 FROM TAPE = '\\.\tape0', TAPE = '\\.\tape1', TAPE = '\\.\tape2'
WITH
MEDIANAME = 'AdventureWorksMediaSet1',
FILE=1,
NORECOVERY;
RESTORE DATABASE AdventureWorks2008R2 FROM TAPE = '\\.\tape0', TAPE = '\\.\tape1', TAPE = '\\.\tape2'
WITH
MEDIANAME = 'AdventureWorksMediaSet1',
FILE=2,
RECOVERY;
GO
For information about the history tables that store information about media sets and their media families and backup sets, see Viewing Information About Backups.
The number of backup media in a media set depends on several factors:
Number of backup devices
Type of backup devices
Number of backup sets
To view the backup sets on a particular backup device