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_STMTVARCHAR(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)