sp_dropdevice (Transact-SQL)

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

Topic link iconTransact-SQL Syntax Conventions


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


  • [ **@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



sp_dropdevice cannot be used inside a transaction.


Requires membership in the diskadmin fixed server role.


The following example drops the tapedump1 tape dump device from the Database Engine.

EXEC sp_dropdevice 'tapedump1';

See Also


sp_addumpdevice (Transact-SQL)
sp_helpdb (Transact-SQL)
sp_helpdevice (Transact-SQL)
System Stored Procedures (Transact-SQL)

Other Resources

Backup Devices
How to: Delete a Backup Device (SQL Server Management Studio)

Help and Information

Getting SQL Server 2005 Assistance