Share via

sys.dm_io_backup_tapes

  • Article

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

ms176071.note(en-US,SQL.90).gifNote:

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.

See Also

Reference

Dynamic Management Views and Functions
I/O Related Dynamic Management Views and Functions

Help and Information

Getting SQL Server 2005 Assistance