Awesome , also while trying to do the same i am stuck in string manipulation for @datestamp and with below it is always giving two quotes ,any way to get around this to get single quotes to generate correct statement?

Declare @BACKUPCMD as varchar(5000)

,@BACKUPDIR1 as varchar(5000)

,@DB as varchar (500)

,@BACKUPFILEEXT as varchar(100)

,@WITHSWITCH as varchar(1000)

select @BACKUPDIR1='d:\mssql\bak'

,@DB='reportserver'

,@BACKUPFILEEXT='.bak'

,@WITHSWITCH='WITH FORMAT, COMPRESSION'

Select @BACKUPCMD='Declare @Datestamp as varchar(10)' + char(13)

Select @BACKUPCMD=@BACKUPCMD + 'SET @Datestamp=Convert(varchar(8),getdate(),112)'+ char(13)

SELECT @BACKUPCMD = @BACKUPCMD + 'Exec(''BACKUP DATABASE [' + @DB + ']' + CHAR(13)

+ 'TO DISK = '+'''' + QUOTENAME(@BACKUPDIR1 + '\' + @DB +'''+@Datestamp+'''+@BACKUPFILEEXT , '''') + CHAR(13)

+ @WITHSWITCH +''''+')'+ CHAR(13)