sp_helpdevice (Transact-SQL)
Applies to: 
SQL Server
Reports information about SQL Server backup devices.
Important
This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use the sys.backup_devices catalog view instead
Transact-SQL syntax conventions
Syntax
sp_helpdevice [ [ @devname = ] N'devname' ]
[ ; ]
Arguments
[ @devname = ] N'devname'
The name of the backup device for which information is reported. @devname is sysname, with a default of NULL.
Return code values
0 (success) or 1 (failure).
Result set
| Column name | Data type | Description |
|---|---|---|
device_name |
sysname | Logical device name. |
physical_name |
nvarchar(260) | Physical file name. |
description |
nvarchar(255) | Description of the device. |
status |
int | A number that corresponds to the status description in the description column. |
cntrltype |
smallint | Controller type of the device:2 = Disk device5 = Tape device |
size |
int | Device size in 2-KB pages. |
Remarks
If @devname is specified, sp_helpdevice displays information about the specified dump device. If @devname isn't specified, sp_helpdevice displays information about all dump devices in the sys.backup_devices catalog view.
Dump devices are added to the system by using sp_addumpdevice.
Permissions
Requires membership in the public role.
Examples
The following example reports information about all dump devices on an instance of SQL Server.
EXEC sp_helpdevice;