/* Backup Databases on a SQL Server 

   Purpose: This Stored Procedure creates a Backup Devices 
   for each database then backs up the database on respective device

   (c) Palak Patel, 2002-05
*/ 

USE MASTER
GO

CREATE PROCEDURE usp_BackupDBs AS

DECLARE @server VARCHAR(20)      --- Server Name
DECLARE @dbn CHAR(50)            --- Database Name
DECLARE @dumpname CHAR(50)       --- Backup Device Name
DECLARE @filename CHAR(100)      --- Physical File Name
DECLARE @today CHAR(8)          --- Date Stamp

DECLARE cur CURSOR FOR
	SELECT NAME FROM MASTER..SYSDATABASES

OPEN cur
FETCH NEXT FROM cur INTO @dbn

SELECT @today = CONVERT(CHAR(8),GETDATE(),112)
SELECT @server = RTRIM(@@SERVERNAME)

WHILE @@FETCH_STATUS=0
BEGIN
  IF RTRIM(@dbn) <> 'TEMPDB'  --- Exclude tempdb
     BEGIN 

     --Backup Device Name is of format: SERVER-Database-DateSpecs
     SET @dumpname = RTRIM(@server + '-' + RTRIM(@dbn) + '-' +RTRIM(@today))
     SET @filename = RTRIM('C:\SQLBACKUP\'+ @server + '-' + RTRIM(@dbn) + '-' + RTRIM(@today) + '.BAK' )

     --Create Backup Device
     EXEC sp_addumpdevice 'disk', @dumpname, @filename

     -- Take Full Backup of the Database (Add any backup options that you need)
     BACKUP DATABASE @dbn TO @dumpname

  END --IF 

  FETCH NEXT FROM cur INTO @dbn
END

CLOSE CUR
DEALLOCATE CUR