Dynamically Script Database Backups

create Proc USP_BackupDatabasesOnPr01 -- exec USP_BackupDatabasesOnPr01 'drive:\FolderPath'
@path nvarchar(4000)

as
/* 
Author:        Shanu Hoosen
Date:        Created:31/01/2011
Execute as:    Exec USP_BackupDatabasesOnPr01 'drive:\FolderPath'

*/
declare @sql nvarchar(4000)
declare @i nvarchar(3)
declare @mx int
declare @DBName nvarchar(500)


select ROW_NUMBER() OVER(ORDER BY name)id,name 
into #d
from sysdatabases
where sid <> 0x01
set @i = (select min(id) from #d)
set @mx = (select max(id)+1 from #d)

while @i <> @mx
begin 
set @DBName=(select '['+name+']' from #d where id = @i)

set @sql = 'BEGIN TRY
            BACKUP DATABASE '+ @DBName +' 
            TO DISK = N'''+@path+ replace(replace (@DBName, '[',''),']','') +'-'+ replace(cast(getdate() as nvarchar(500)),':','')+'.bak'''+
            ' WITH NOFORMAT, NOINIT,     
             NAME = N'''+@DBName +'-Full Database Backup'''+','
            +'SKIP, NOREWIND, NOUNLOAD, STATS = 10
            END TRY
            BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber,
                ERROR_SEVERITY() AS ErrorSeverity,
                ERROR_STATE() AS ErrorState,
                ERROR_PROCEDURE() AS ErrorProcedure,
                ERROR_LINE() AS ErrorLine,
                ERROR_MESSAGE() AS ErrorMessage
            END CATCH'    


            --print(@sql)
            exec(@sql)



set @i =@i+1
end
drop table #d