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. This field is shared between backup and restore process. It may contain the original backup destination path or the original restore source path. Depending on whether backup or restore occurred first on a server for a database. Note that consecutive restores from the same backup file will not update the path regardless of it's location at restore time. Because of this, physical_device_name field cannot be used to see the restore path used.
device_type tinyint Type of backup device:

2 = Disk

5 = Tape

7 = Virtual device

9 = Azure Storage

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)