| title | sp_helpdevice (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 06/10/2016 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.service | |||
| ms.component | system-stored-procedures | ||
| ms.reviewer | |||
| ms.suite | sql | ||
| ms.technology |
|
||
| ms.tgt_pltfrm | |||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 1a5eafa7-384e-4691-ba05-978eb73bbefb | ||
| caps.latest.revision | 29 | ||
| author | stevestein | ||
| ms.author | sstein | ||
| manager | craigg | ||
| ms.workload | Inactive |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
Reports information about Microsoft® SQL Server™ backup devices.
Important
[!INCLUDEssNoteDepFutureAvoid] We recommend that you use the sys.backup_devices catalog view instead
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 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. |
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 [!INCLUDEssNoVersion].
EXEC sp_helpdevice;
sp_addumpdevice (Transact-SQL)
sp_dropdevice (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)
System Stored Procedures (Transact-SQL)