sys.dm_io_backup_tapes (Transact-SQL)


Updated: June 10, 2016

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

Returns the list of tape devices and the status of mount requests for backups.

Column nameData typeDescription
physical_device_namenvarchar(520)Name of the actual physical device on which a backup can be taken. Is not nullable.
logical_device_namenvarchar(256)User-specified name for the drive (from sys.backup_devices). NULL if no user-specified name is available. Is nullable.
statusintStatus of the tape:

1 = Open, available for use

2 = Mount pending

3 = In use

4 = Loading

 Note: While a tape is being loaded (status = 4), the media label is not read yet. Columns that copy media-label values, such as media_sequence_number, show anticipated values, which may differ from the actual values on the tape. After the label has been read, status changes to 3 (in use), and the media-label columns then reflect the actual tape that is loaded.

Is not nullable.
status_descnvarchar(520)Description of the tape status:





Is not nullable.
mount_request_timedatetimeTime at which mount was requested. NULL if no mount is pending (status!=2). Is nullable.
mount_expiration_timedatetimeTime at which mount request will expire (time-out). NULL if no mount is pending (status!=2). Is nullable.
database_namenvarchar(256)Database that is to be backed up onto this device. Is nullable.
spidintSession ID. This identifies the user of the tape. Is nullable.
commandintCommand that performs the backup. Is nullable.
command_descnvarchar(120)Description of the command. Is nullable.
media_family_idintIndex of media family (1...n), n is the number of media families in the media set. Is nullable.
media_set_namenvarchar(256)Name of the media set (if any) as specified by the MEDIANAME option when the media set was created). Is nullable.
media_set_guiduniqueidentifierIdentifier that uniquely identifies the media set. Is nullable.
media_sequence_numberintIndex of volume within a media family (1...n). Is nullable.
tape_operationintTape operation that is being performed:

1 = Read

2 = Format

3 = Init

4 = Append

Is nullable.
tape_operation_descnvarchar(120)Tape operation that is being performed:





Is nullable.
mount_request_typeintType of the mount request:

1 = Specific tape. The tape identified by the media_* fields is required.

2 = Next media family. The next media family not yet restored is requested. This is used when restoring from fewer devices than there are media families.

3 = Continuation tape. The media family is being extended, and a continuation tape is requested.

Is nullable.
mount_request_type_descnvarchar(120)Type of the mount request:




Is nullable.

The user must have VIEW SERVER STATE permission on the server.

Dynamic Management Views and Functions (Transact-SQL)
I O Related Dynamic Management Views and Functions (Transact-SQL)

Community Additions