Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

backupmediafamily (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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.

Applies to: SQL Server (SQL Server 2008 through current version).

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.

Community Additions

ADD
Show:
© 2015 Microsoft