Creating Backup Devices

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

USE msdb
GO

/**********************************************************************************
Stored Procedure......: pCreateBackupDevices
Objetivo..............: Criar devices para backup full, differential e log

----------  --------------------  -------------------------------------------------
Data    Programador  Observação
----------  --------------------  -------------------------------------------------
22/07/2002  Paulo Barros  Created

**********************************************************************************/CREATE PROCEDURE pCreateBackupDevices AS
/*********************************************************************************/
Declare @cDBName varchar(50)

DECLARE curDB CURSOR FOR 
select name from master.dbo.sysdatabases
where name not in ('tempdb','master','model','msdb')
 
OPEN curDB

FETCH NEXT FROM curDB 
INTO @cDBName

WHILE @@FETCH_STATUS = 0

BEGIN

declare @cComando varchar(200)


if     not exists (select * from master.dbo.sysdevices where phyname = 'd:\mssql\backup\' + @cDBName + '_Full.bak')
   and not exists (select * from master.dbo.sysdevices where name = @cDBName + '_Full')
   begin
set @cComando = "sp_addumpdevice 'disk','" + @cDBName + "_Full',
                 'd:\mssql\backup\" + @cDBName + "_Full.bak'"
EXEC (@cComando)
   end

if     not exists (select * from master.dbo.sysdevices where phyname = 'd:\mssql\backup\' + @cDBName + '_Log.bak')
   and not exists (select * from master.dbo.sysdevices where name = @cDBName + '_Log')
   begin
set @cComando = "sp_addumpdevice 'disk','" + @cDBName + "_Log',
                 'd:\mssql\backup\" + @cDBName + "_Log.bak'"
EXEC (@cComando)
   end

if     not exists (select * from master.dbo.sysdevices where phyname = 'd:\mssql\backup\' + @cDBName + '_Diff.bak')
   and not exists (select * from master.dbo.sysdevices where name = @cDBName + '_Diff')
   begin
set @cComando = "sp_addumpdevice 'disk','" + @cDBName + "_Diff',
                 'd:\mssql\backup\" + @cDBName + "_Diff.bak'"
EXEC (@cComando)
   end


FETCH NEXT FROM curDB 
INTO @cDBName

END

DEALLOCATE curDB