This post is part of a blog series which focuses on translating compatibility views in SQL Server to their respective dynamic management objects.  You can find a list of all of the deprecated views in the introduction post.

The compatibility view sysopentapes returns one row for tape devices that are currently connected and open on a SQL Server instance.

The compatibility view has been replaced by the dynamic management view sys.dm_io_backup_tapes.  The dynamic management view similarly returns a list of open tape devices.  Beyond that, sys.dm_io_backup_tapes also returns information on the status of the tape device.

Query Via sysopentapes

The compatibility view sysopentapes returns a single column in its results.  A query to return this column is provided in Listing 1.

--Listing 1 – Query for sys.sysopentapes
SELECT openTape
FROM sys.sysopentapes

Query via sys.dm_io_backup_tapes

As mentioned in the introduction, the dynamic management view, sys.dm_io_backup_tapes, that replaces sysopentapes provides much more information than the compatibility view.  The additional columns provide information about the status of mounting the tape drive and timing around the mounting.  The current session and command associated with the tape drive are provided.  Also, information about the media sets and current operations being performed are included.  The query provided in Listing 2 provides access to all of the information in the DMV.  Overall, the DMV provides greater, and necessary, insight into backup operations being performed on tape media.

--Listing 2 – Query for sys.dm_io_backup_tapes
SELECT physical_device_name AS openTape
, logical_device_name
, status
, status_desc
, mount_request_time
, mount_expiration_time
, database_name
, spid
, command
, command_desc
, media_family_id
, media_set_name
, media_set_guid
, media_sequence_number
, tape_operation
, tape_operation_desc
, mount_request_type
, mount_request_type_desc
FROM sys.dm_io_backup_tapes

Summary

In this post, we compared the compatibility view sysopentapes with the dynamic management view sys.dm_io_backup_tapes.  Any application or process using the compatibility view is vastly under informed for whatever purpose it is currently being leveraged.  After reading all of this, do you see any reason to continue using sysopentapes?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Related posts:

1. Lost in Translation – Deprecated System Tables – sysperfinfo
2. Lost in Translation – Deprecated System Tables – sysmessages
3. Lost in Translation – Deprecated System Tables – sysoledbusers