Skip to content

Latest commit

 

History

History
81 lines (58 loc) · 2.72 KB

sp-helpdevice-transact-sql.md

File metadata and controls

81 lines (58 loc) · 2.72 KB
Raw
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sp_helpdevice (Transact-SQL)
sp_helpdevice reports information about SQL Server backup devices.
markingmyname
maghan
randolphwest
05/14/2024
sql
system-objects
reference
sp_helpdevice
sp_helpdevice_TSQL
sp_helpdevice
TSQL

sp_helpdevice (Transact-SQL)

[!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

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 [!INCLUDE ssNoVersion].

EXEC sp_helpdevice;

Related content