sp-dropdevice-transact-sql.md

description	sp_dropdevice (Transact-SQL)
title	sp_dropdevice (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	08/09/2016
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology	system-objects
ms.topic	language-reference
f1_keywords	sp_dropdevice_TSQL sp_dropdevice
dev_langs	TSQL
helpviewer_keywords	backup devices [SQL Server], deleting sp_dropdevice
ms.assetid	c8b07189-7c35-414b-acc1-45bd6e7e17c3
author	markingmyname
ms.author	maghan

sp_dropdevice (Transact-SQL)

[!INCLUDE SQL Server]

Drops a database device or backup device from an instance of the [!INCLUDEssDEversion2005], deleting the entry from master.dbo.sysdevices.

Transact-SQL Syntax Conventions

Syntax

  
sp_dropdevice [ @logicalname = ] 'device'   
    [ , [ @delfile = ] 'delfile' ]  

Arguments

[ @logicalname = ] 'device' Is the logical name of the database device or backup device as listed in master.dbo.sysdevices.name. device is sysname, with no default.

[ @delfile = ] 'delfile' Specifies whether the physical backup device file should be deleted. delfile is varchar(7). If specified as DELFILE, the physical backup device disk file is deleted.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

sp_dropdevice cannot be used inside a transaction.

Permissions

Requires membership in the diskadmin fixed server role.

Examples

The following example drops the tapedump1 tape dump device from the [!INCLUDEssDE].

EXEC sp_dropdevice 'tapedump1';  

See also

Backup Devices (SQL Server)
Delete a Backup Device (SQL Server)
sp_addumpdevice (Transact-SQL)
sp_helpdb (Transact-SQL)
sp_helpdevice (Transact-SQL)
System Stored Procedures (Transact-SQL)