Updated: June 10, 2016
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).|
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.