Edit

Share via

sp_helpdevice (Transact-SQL)

  • Article

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 device
5 = 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;

Related content