sp_helpdevice (Transact-SQL)


Updated: June 10, 2016

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Reports information about Microsoft® SQL Server™ backup devices.

System_CAPS_ICON_important.jpg Important

This feature will be removed in a future version of Microsoft 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

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

sp_helpdevice [ [ @devname = ] 'name' ]  

[ @devname = ] 'name'
Is the name of the backup device for which information is reported. The value of name is always sysname.

0 (success) or 1 (failure)

Column nameData typeDescription
device_namesysnameLogical device name.
physical_namenvarchar(260)Physical file name.
descriptionnvarchar(255)Description of the device.
statusintA number that corresponds to the status description in the description column.
cntrltypesmallintController type of the device:

2 = Disk device

5 = Tape device
sizeintDevice size in 2-KB pages.

If name is specified, sp_helpdevice displays information about the specified dump device. If name is not 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.

Requires membership in the public role.

The following example reports information about all dump devices on an instance of SQL Server.

EXEC sp_helpdevice;  

sp_addumpdevice (Transact-SQL)
sp_dropdevice (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)

Community Additions