sp_helpdevice (Transact-SQL)

Article
02/26/2024

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 = ] 'name' ]

Arguments

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

Return Code Values

0 (success) or 1 (failure)

Result Sets

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 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.

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;