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

• RESTORE LABELONLY (Transact-SQL)
  

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 AdventureWorks database using three tape drives as backup devices:

BACKUP DATABASE AdventureWorks
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:

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 AdventureWorks
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:

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 AdventureWorks 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 AdventureWorks FROM TAPE = '\\.\tape0', TAPE = '\\.\tape1', TAPE = '\\.\tape2'
   WITH 
   MEDIANAME = 'AdventureWorksMediaSet1',
   FILE=1, 
   NORECOVERY;
RESTORE DATABASE AdventureWorksFROM 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

• How to: View the Properties and Contents of a Logical Backup Device (SQL Server Management Studio)
  
• RESTORE HEADERONLY (Transact-SQL)
  

Concepts

Other Resources

Help and Information