backupmediafamily (Transact-SQL)

Contains one row for each media family. If a media family resides in a mirrored media set, the family has a separate row for each mirror in the media set. This table is stored in the msdb database.

Column name

Data type

Description

media_set_id

int

Unique identification number that identifies the media set of which this family is a member. References backupmediaset(media_set_id)

family_sequence_number

tinyint

Position of this media family in the media set.

media_family_id

uniqueidentifier

Unique identification number that identifies the media family. Can be NULL.

media_count

int

Number of media in the media family. Can be NULL.

logical_device_name

nvarchar(128)

Name of this backup device in sys.backup_devices.name. If this is a temporary backup device (as opposed to a permanent backup device that exists in sys.backup_devices), the value of logical_device_name is NULL.

physical_device_name

nvarchar(260)

Physical name of the backup device. Can be NULL.

device_type

tinyint

Type of backup device:

2 = Disk

5 = Tape

7 = Virtual device

105 = A permanent backup device.

Can be NULL.

All permanent device names and device numbers can be found in sys.backup_devices.

physical_block_size

int

Physical block size used to write the media family. Can be NULL.

mirror

tinyint

Mirror number (0-3).

Remarks

RESTORE VERIFYONLY FROM backup_device WITH LOADHISTORY populates the columns of the backupmediaset table with the appropriate values from the media-set header.

To reduce the number of rows in this table and in other backup and history tables, execute the sp_delete_backuphistory stored procedure.

See Also

Reference

backupfile (Transact-SQL)

backupfilegroup (Transact-SQL)

backupmediaset (Transact-SQL)

backupset (Transact-SQL)

System Tables (Transact-SQL)

Other Resources

Backup and Restore Tables (Transact-SQL)