Select @SQL = 'BACKUP DATABASE [' + @DBName + '] TO  DISK = N''' + @Path + '' + @DBName + '_backup_' + @Date + '.bak'' WITH NOFORMAT, INIT,  NAME = N''' + @DBName + '_backup_' + @Date + ''', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10 '

EXEC SP_ExecuteSql @SQLToExecute = @SQL

DECLARE @FOLDERPATH VARCHAR(500)
DECLARE @FOLDERPATHWITHNAME VARCHAR(500)
Declare @NAME Varchar(200)
Declare @DATE Varchar(200)
Declare @TIME Varchar(200)
Declare @TIME1 Varchar(200)

-------------------------------------------------------
--JUST NEED TO UPDATE BELOW PATH FOR DB BACKUP LOCATION
-------------------------------------------------------

Set @FOLDERPATH = 'D:TempDsingh'

select @TIME1 = Convert(time, getdate())

select @DATE = (select convert(varchar,getDate(),112))

select @TIME = ((select Convert(Varchar, DATEPART(hh, @TIME1)))
               + (Select CONVERT(Varchar, DATEPART(ss, @TIME1))))

Select @NAME = 'mydatabase_backup' + '_' + @DATE + @TIME

Set @FOLDERPATHWITHNAME = @FOLDERPATH + @NAME + '.bak'

EXECUTE master.dbo.xp_create_subdir @FOLDERPATH

BACKUP DATABASE [TEST_1_Smarttrack_03202014]
       TO  DISK = @FOLDERPATHWITHNAME WITH NOFORMAT, NOINIT,
       NAME = @NAME, SKIP, REWIND, NOUNLOAD,  STATS = 10