SQL Server backup devices

Question

I'm trying to find out how to detect if a backup device exists using T-SQL.

This is how I create my backup device:

EXEC master.dbo.sp_addumpdevice 
 @devtype = N'disk', 
 @logicalname = N'TestTest', 
 @physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PTC_SQLPROD\MSSQL\Backup\TestTest.bak'

but I can't find how to detect if it already exists first.

Answer 1

The sys.backup_devices table should contain details on existing backup devices, so try checking to see if there's a row there first:

IF NOT EXISTS (SELECT NULL
 FROM sys.backup_devices
 WHERE [Name] = N'TestTest'
 AND [Type] = 2) -- 2 = 'Disk'; see MSDN
 EXEC master.dbo.Sp_addumpdevice
 @devtype = N'disk',
 @logicalname = N'TestTest',
 @physicalname = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.PTC_SQLPROD\MSSQL\Backup\TestTest.bak'

The documentation for the sys.backup_devices table can be found here: http://msdn.microsoft.com/en-us/library/ms178018%28v=sql.100%29.aspx