sys.dm_io_backup_tapes (Transact-SQL)
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: AVAILABLE MOUNT PENDING IN USE LOADING MEDIA 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 Is nullable. |
| tape_operation_desc | nvarchar(120) | Tape operation that is being performed: READ FORMAT INIT APPEND Is nullable. |
| 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. Is nullable. |
| mount_request_type_desc | nvarchar(120) | Type of the mount request: SPECIFIC TAPE NEXT MEDIA FAMILY CONTINUATION VOLUME Is nullable. |
Permissions
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.
See also
Dynamic Management Views and Functions (Transact-SQL)
I/O Related Dynamic Management Views and Functions (Transact-SQL)