Backup a database

/**********************************************************/
/* Program Name		: BackupDatabase		  */
/* Date Written		: October 13, 2001		  */
/* Description		: This program helps backup 	  */
/*			  database			  */
/* Parameters		: Databasename, Path for backup   */
/**********************************************************/

IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = 'USP_BACKUPDATABASE' 
	   AND 	  type = 'P')
    DROP PROCEDURE USP_BACKUPDATABASE
GO

CREATE PROCEDURE  USP_BACKUPDATABASE @DBNAME VARCHAR(50) , @DIRPATH VARCHAR(250)
AS

DECLARE
@USEMASTER VARCHAR(1000),
@DROPDEVICE VARCHAR(8000),
@CREATEDEVICE_STMT VARCHAR(1000),
@BACKUP_STMT	VARCHAR(8000)



SET @USEMASTER = ''
SET @DROPDEVICE = ''
SET @CREATEDEVICE_STMT = ''
SET @BACKUP_STMT = ''

-- DROP EXISTING DEVICE

SET @DROPDEVICE  = ' IF EXISTS(SELECT name 
	  FROM 	 MASTER.DBO.SYSDEVICES 
	  WHERE  name = '''+ @DBNAME + ''')
    EXEC SP_DROPDEVICE ' + @DBNAME

EXEC(@DROPDEVICE)



-- CREATE BACKUP DEVICE

SET @USEMASTER = 'use master'
EXEC(@USEMASTER)


SET @CREATEDEVICE_STMT = 'SP_ADDUMPDEVICE '  + '''DISK'''   + ', ' + '''' + @DBNAME + '''' + ', ' + '''' + @DIRPATH + ''''
EXEC(@CREATEDEVICE_STMT)


-- BACKUP THE DATABASE

SET @BACKUP_STMT = 'BACKUP DATABASE ' + @DBNAME + ' TO ' + @DBNAME + ' WITH INIT '
EXEC(@BACKUP_STMT)