SQL SERVER: DB backup Script

--EXEC BackupDBUtility @Path = 'D:\DBBackup\My\', @dbName = 'Test1'

Create proc BackupDBUtility(@Path VARCHAR(100), @DBName VARCHAR(20))
AS
begin
SET NOCOUNT ON

DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
declare @Sql varchar(4000)

-- Get the current date using style 120, remove all dashes, spaces, and colons
 SELECT @Now = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(50), GETDATE(), 120), '-', ''), ' ', ''), ':', '')

 -- Build the .BAK path and file name
 SELECT @filename = @Path + @DBName + '\' + @DBName + '_' + @Now + '.BAK'
 -- Build the dir command that will check to see if the directory exists
 SELECT @Path =  @Path + @DBName

 --create the folder Path
 DECLARE @Dir TABLE (subdirectory nvarchar(255), depth INT)
 INSERT INTO @Dir(subdirectory, depth)
 EXEC master.sys.xp_dirtree @Path

 IF NOT EXISTS (SELECT 1 FROM @Dir WHERE subdirectory = @Path)
 EXEC master.dbo.xp_create_subdir @Path
 --Take backup
BACKUP DATABASE @DBName TO DISK = @filename

end