Hi Ken,

Sorry about that.

please try below:

USE [master]

GO

/****** Object: StoredProcedure [dbo].[USP_BackupDatabasesOnPr01] Script Date: 02/14/2011 09:04:17 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

alter Proc [dbo].[USP_BackupDatabasesOnPr01] -- exec USP_BackupDatabasesOnPr01 'V:\Data\January Backups\'

@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)

--declare @path nvarchar(4000)

--set @path = 'V:\Data\January Backups\'

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,

END CATCH'

--print(@sql)

exec(@sql)

set @i =@i+1

end

drop table #d