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 sysdevices returns one row for every named backup device.  Backup devices can be either disk or tape files.  In the olden days, the devices also included database files; which are now returned through sys.master_files.  Devices can be added to the list through sp_adddumpdevice; which can then be used as a named location during backups.

The replacement for sysdevices is the catalog view sys.backup_devices.  As mentioned, only backup devices are now stored in the compatibility view sysdevices.  For the catalog view, the only information returned and possible now is backup devices that are registered through sp_adddumpdevice.  The current available device types are disk, tape, and virtual devices.

Status Columns

In a continuing trend with many other compatibility views, the view sysdepends does have a couple columns that need to be decoded when it is queried.  These columns are status and cntrltype.  Fortunately, both of the columns are documented in Books Online.

Looking at the first column to decode in sysdepends, which is status, there are two general pieces of information that are retrieved.  First the value indicates the type of device that is being referenced.  Second, the column contains a value to identify whether the device is read only.  The bitwise values stored in the column are the following:

• 0×1: Default disk
• 0×2: Physical disk
• 0×4: Logical disk
• 0×8: Skip header
• 0×10: Backup file
• 0×12: Serial writes
• 0×1000: read only

The second column is cntrltype.  This column defines the type of controller that is used to interface with the device.  The possible values for this column are:

• 0: Non-CD-ROM database file
• 2: Disk backup file
• 3 – 4: Diskette backup file
• 5: Tape backup file
• 6: Named-pipe file

Query Via sysdepends

As with many of the compatibility views, queries against sysdepends are fairly simple to created.  For the most part, the query just returns the values in the compatibility view with the addition of three expressions to break down the status and cntrltype columns.  There are three columns that you won’t, or should not, be using since they only report the value 0 in SQL Server 2012 database.  These are size, low, and high.  Low and high are included for backwards compatibility.  While not documented as such, size appears to fit this description as well since it references the older 2KB style database pages.

Listing 1 – Query for sys.sysdevices
SELECT d.name
,d.status
,d.cntrltype
,d.phyname
,CASE WHEN CONVERT(INT,d.status & 0x1) = 1 THEN 'Default disk'
WHEN CONVERT(INT,d.status & 0x2) = 2 THEN 'Physical disk'
WHEN CONVERT(INT,d.status & 0x4) = 4 THEN 'Logical disk'
WHEN CONVERT(INT,d.status & 0x8) = 8 THEN 'Skip header'
WHEN CONVERT(INT,d.status & 0x10) = 16 THEN 'Backup file'
WHEN CONVERT(INT,d.status & 0x12) = 32 THEN 'Serial writes'
END AS device_type
,CASE WHEN CONVERT(INT,d.status & 0x1000) = 4096 THEN 1 ELSE 0 END AS is_read_only
,CASE WHEN cntrltype = 0 THEN 'Non-CD-ROM database file'
WHEN cntrltype = 2 THEN 'Disk backup file'
WHEN cntrltype IN (3,4) THEN 'Diskette backup file'
WHEN cntrltype = 5 THEN 'Tape backup file'
WHEN cntrltype = 6 THEN 'Named-pipe file' END AS controller_type
FROM sysdevices d

Query via sys.backup_devices

Mapping sys.backup_devices to sysdevices is a relatively simple task.  The replacement catalog view only contains four columns.  Two of these columns represent the same information, type and type_desc; which map to the cntrltype column in sysdevices.  The information on read only and device type, does not apply to sys.backup_devices since it is technically the backup subset of data from the previous view.  This information being a subset is not an issue, though, since the other types of information are no longer relevant within SQL Server.

Listing 2 – Query for sys.backup_devices
SELECT name
,physical_name
,type
,type_desc
FROM sys.backup_devices

Summary

In this post, we discussed the use of sys.backup_devices instead of sysdevices.  Using sys.backup_devices provides all of the information needed from sysdevices that is still a part of SQL Server.  Though, that functionality is not frequently used.  After reading all of this, do you see any reason to continue using sysdevices?  Is there anything missing from this post that people continuing to use the compatibility view should know?

Follow me on Twitter at StrateSQL.

Original article: Lost in Translation – Deprecated System Tables – sysdevices

©2012 Strate SQL. All Rights Reserved.

Related posts:

1. Lost in Translation – Deprecated System Tables – sysaltfiles
2. Lost in Translation – Deprecated System Tables – sysconfigures
3. Lost in Translation – Deprecated System Tables – syscurconfigs