| title | sp_helpdevice (Transact-SQL) | ||
|---|---|---|---|
| description | sp_helpdevice reports information about SQL Server backup devices. | ||
| author | markingmyname | ||
| ms.author | maghan | ||
| ms.reviewer | randolphwest | ||
| ms.date | 06/23/2025 | ||
| ms.service | sql | ||
| ms.subservice | system-objects | ||
| ms.topic | reference | ||
| f1_keywords |
|
||
| helpviewer_keywords |
|
||
| dev_langs |
|
[!INCLUDE SQL Server]
Reports information about [!INCLUDE ssnoversion-md] backup devices.
Important
[!INCLUDE ssNoteDepFutureAvoid] We recommend that you use the sys.backup_devices catalog view instead
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sp_helpdevice [ [ @devname = ] N'devname' ]
[ ; ]
The name of the backup device for which information is reported. @devname is sysname, with a default of NULL.
0 (success) or 1 (failure).
| 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. |
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.
Requires membership in the public role.
The following example reports information about all dump devices on an instance of [!INCLUDE ssNoVersion].
EXECUTE sp_helpdevice;