Applies to: SQL Server
Returns the list of tape devices and the status of mount requests for backups.
|Column name||Data type||Description|
|physical_device_name||nvarchar(520)||Name of the actual physical device on which a backup can be taken. Is not nullable.|
|logical_device_name||nvarchar(256)||User-specified name for the drive (from sys.backup_devices). NULL if no user-specified name is available. Is nullable.|
|status||int||Status 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_desc||nvarchar(520)||Description of the tape status:
Is not nullable.
|mount_request_time||datetime||Time at which mount was requested. NULL if no mount is pending (status!=2). Is nullable.|
|mount_expiration_time||datetime||Time at which mount request will expire (time-out). NULL if no mount is pending (status!=2). Is nullable.|
|database_name||nvarchar(256)||Database that is to be backed up onto this device. Is nullable.|
|spid||int||Session ID. This identifies the user of the tape. Is nullable.|
|command||int||Command that performs the backup. Is nullable.|
|command_desc||nvarchar(120)||Description of the command. Is nullable.|
|media_family_id||int||Index of media family (1...n), n is the number of media families in the media set. Is nullable.|
|media_set_name||nvarchar(256)||Name of the media set (if any) as specified by the MEDIANAME option when the media set was created). Is nullable.|
|media_set_guid||uniqueidentifier||Identifier that uniquely identifies the media set. Is nullable.|
|media_sequence_number||int||Index of volume within a media family (1...n). Is nullable.|
|tape_operation||int||Tape operation that is being performed:
1 = Read
2 = Format
3 = Init
4 = Append
|tape_operation_desc||nvarchar(120)||Tape operation that is being performed:
|mount_request_type||int||Type 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.
|mount_request_type_desc||nvarchar(120)||Type of the mount request:
NEXT MEDIA FAMILY
The user must have VIEW SERVER STATE permission on the server.
Permissions for SQL Server 2022 and later
Requires VIEW SERVER PERFORMANCE STATE permission on the server.