backupmediaset (Transact-SQL)


Updated: August 9, 2016

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

Contains one row for each backup media set. This table is stored in the msdb database.

Column nameData typeDescription
media_set_idintUnique media set identification number. Identity, primary key.
media_uuiduniqueidentifierThe UUID of the media set. All Microsoft SQL Server media sets have a UUID.

For earlier versions of SQL Server, however, if a media set contains only one media family, the media_uuid column might be NULL (media_family_count is 1).
media_family_counttinyintNumber of media families in the media set. Can be NULL.
namenvarchar(128)Name of the media set. Can be NULL.

For more information, see MEDIANAME and MEDIADESCRIPTION in BACKUP (Transact-SQL).
descriptionnvarchar(255)Textual description of the media set. Can be NULL.

For more information, see MEDIANAME and MEDIADESCRIPTION in BACKUP (Transact-SQL).
software_namenvarchar(128)Name of the backup software that wrote the media label. Can be NULL.
software_vendor_idintIdentification number of the software vendor that wrote the backup media label. Can be NULL.

The value for Microsoft SQL Server is hexadecimal 0x1200.
MTF_major_versiontinyintMajor version number of Microsoft Tape Format used to generate this media set. Can be NULL.
mirror_counttinyintNumber of mirrors in the media set.
is_password_protectedbitIs the media set password protected:

0 = Not protected

1 = Protected
is_compressedbitWhether the backup is compressed:

0 = Not compressed

1 = Compressed

During an msdb upgrade, this value is set to NULL. which indicates an uncompressed backup.
is_encryptedBitWhether the backup is encrypted:

0 = Not encrypted

1 = Encrypted

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)
backupmediafamily (Transact-SQL)
backupset (Transact-SQL)
System Tables (Transact-SQL)

Community Additions