if exists (select 'SP exists' from sysobjects where name = 'up_autobackup_dbs' and xtype = 'p')
	drop proc up_autobackup_dbs
go

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

create procedure up_autobackup_dbs
	@dbname varchar(255),
	@backuppath varchar(255)

	
--	Created By:	Regan Galbraith
--	Created on:	2004-07-20
--	
--	Purpose:	This stored procedure will create a "scrubbed" database backup
--
--			It creates backups using a specific naming standard for the backup, namely DBNAME_scrubbed.bak
--			This is used as part of a process to automate restoring and scrubbing of database backups.
--
--	Example:
--		Usage:		exec up_autobackup_dbs 'Model','D:\'
--
--	Possible future additions:
--		1> 	Currently, the usage is for 1 call, and and external process (cursor) is needed to run the SP
--			providing the required database names. A version could be made that runs for all DBs
--
--	Change Control:	v1.0 - original version 2004-07-20	
--			v1.1 - change 2004-07-26 - Regan Galbraith
--				added [ ] to cater for dbs named with numerics or reserved words
--


as

DECLARE @ReturnCode	int
Set @ReturnCode = 0

begin
set nocount on
set quoted_identifier off
declare @backupSQL varchar(512)
set @backupSQL = '@returncode=backup database ['+@dbname+'] to disk = '''+@backuppath+@dbname+'_scrubbed.bak'' with init'

--print @backupSQL
exec @backupSQL
--also tried exec @returncode = @backupSQL --naturally, didn't have the @returnCode in the @BackupSQL then.
--Also tried exec @returncode=exec @backupSQL

If @ReturnCode <> 0 
	Begin
	Print 'Error backing up DBs'
	set @ReturnCode = 1
end
RETURN @ReturnCode

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

declare @Rc int

set @rc=9
exec @Rc=up_autobackup_dbs 'Model','c:\'
print @rc