SP for DB Back Up

-- T-SQL dynamic stored procedure for database backup

USE AdventureWorks2008;

GO

CREATE PROC sprocFullBackup

           @DatabaseName SYSNAME,

           @Folder       VARCHAR(256),

           @FileName     SYSNAME

AS

  BEGIN

    DECLARE  @DynaSql NVARCHAR(MAX)

    IF RIGHT(@Folder,1) != '\'

      SET @Folder = @Folder + '\'

    SELECT @DynaSql = 'BACKUP DATABASE ' + @DatabaseName + ' TO DISK = ' +

                   CHAR(39) + @Folder + @FileName + CHAR(39)

    PRINT @DynaSql

    EXEC sp_executeSQL @DynaSql

  END

GO

-- Execute dynamic stored procedure - Transact-SQL

exec sprocFullBackup 'AdventureWorks2008',

                     'f:\data\AdventureWorks2008\backup\',

                     'test1.bak'