Edit

sys.dm_io_backup_tapes (Transact-SQL)

  • Article

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)