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