Edit

Share via

sp_dropdevice (Transact-SQL)

  • Article
  • 11/29/2023

Applies to: SQL Server

Drops a database device or backup device from a SQL Server Database Engine instance, deleting the entry from master.dbo.sysdevices.

Transact-SQL syntax conventions

Syntax

sp_dropdevice
    [ @logicalname = ] N'logicalname'
    [ , [ @delfile = ] 'delfile' ]
[ ; ]

Arguments

[ @logicalname = ] N'logicalname'

The logical name of the database device or backup device as listed in master.dbo.sysdevices.name. @logicalname is sysname, with no default.

[ @delfile = ] 'delfile'

Specifies whether the physical backup device file should be deleted. @delfile is varchar(7), with a default of NULL. If specified as DELFILE, the physical backup device disk file is deleted.

Return code values

0 (success) or 1 (failure).

Result set

None.

Remarks

sp_dropdevice can't 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 Database Engine.

EXEC sp_dropdevice 'tapedump1';

Related content