SQL Server backup devices

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.

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

StackExchange Database Administrators Q#27946, answer score: 3