backupmediafamily (Transact-SQL)

 

Updated: June 10, 2016

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 nameData typeDescription
media_set_idintUnique identification number that identifies the media set of which this family is a member. References backupmediaset(media_set_id)
family_sequence_numbertinyintPosition of this media family in the media set.
media_family_iduniqueidentifierUnique identification number that identifies the media family. Can be NULL.
media_countintNumber of media in the media family. Can be NULL.
logical_device_namenvarchar(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_namenvarchar(260)Physical name of the backup device. Can be NULL.
device_typetinyintType 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_sizeintPhysical block size used to write the media family. Can be NULL.
mirrortinyintMirror 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.

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

Community Additions

ADD
Show: