backupmediafamily (Transact-SQL)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 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).


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

Backup and Restore Tables (Transact-SQL)
backupfile (Transact-SQL)
backupfilegroup (Transact-SQL)
backupmediaset (Transact-SQL)
backupset (Transact-SQL)
System Tables (Transact-SQL)