if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[s_RestoreDatabase]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[s_RestoreDatabase]
GO

create proc s_RestoreDatabase
@SourcePath 	varchar(200) ,
@archivePath	varchar(200) ,
@DataPath 	varchar(200) ,
@LogPath 	varchar(200) ,
@recover	varchar(10) ,		-- recover, norecovery, standby
@recipients	varchar(128) = null ,
@Database	varchar(128) = null ,	-- only restore this database
@Debug		int = 0
as

/*
exec s_RestoreDatabase
	@SourcePath = 'c:\atest\' ,
	@archivePath = 'c:\atest\archive\' ,
	@DataPath = 'c:\atest\' ,
	@LogPath = 'c:\atest\' ,
	@recover = 'norecovery' ,
	@recipients = ''
*/	

/*
Get all files from directory (they will be *.bak)
The process is controlled by the files in the directory
If there is a full backup then restore it.
If there is a diff then restore it next.
Restore any logs
Recover database if necessary
*/

declare @dbname		varchar(128) ,
	@cmd 		varchar(2000) ,
	@filename	varchar(128) ,
	@s		varchar(128) ,
	@t		varchar(128) ,
	@sql		nvarchar(2000)
	
	create table #files (lname varchar(128), pname VARCHAR(128), type varchar(10), fgroup varchar(128), size varchar(50), maxsize varchar(50))
	create table #dir (s varchar(2000))
	
	-- get list of files in directory
	select @cmd = 'dir /B ' + @SourcePath + '*.bak'
	insert #dir exec master..xp_cmdshell @cmd
	
	delete	#dir 
	where s is null
	or s not like '%.bak'
	or 	(	s not like '%^_Full^_%' escape '^'
		and	s not like '%^_Log^_%' escape '^'
		)
	
	if @Database is not null
		delete	#dir 
		where s not like @Database + '^_%' escape '^'
	
if @Debug = 1
select * from #dir
	
	-- deal with each database in turn
	while exists (select * from #dir)
	begin
		-- any with a full first
		select @dbname = null
		select	top 1 @dbname = left(s,charindex('_Full_', s) - 1) 
		from 	#dir 
		where 	charindex('_Full_', s) <> 0
		order by s
		
		-- no full - get log
		if @dbname is null
		begin
			select @dbname = null
			select	top 1 @dbname = left(s,charindex('_Log_', s) - 1) 
			from 	#dir 
			where 	charindex('_Log_', s) <> 0
			order by s
		end
		
		-- find the last full backup for this db
		select	@filename = null
		select 	@filename = max(s)
		from	#dir
		where	s like @dbname + '_Full_%.bak'
		
		-- archive everything for this db that appears before this file
		select @s = ''
		while @s is not null and @filename is not null
		begin
			select 	top 1 @s = s
			from	#dir
			where	s like @dbname + '%'
			order by right(s, 20)
			
			if @s = @filename
			begin
				select @s = null
			end
			else
			begin
				-- move to archive
				select @cmd = 'move ' + @SourcePath + @s + ' ' + @archivePath + @s
				exec master..xp_cmdshell @cmd
				delete #dir where s = @s
			end
		end
		
		-- now we can go through each file in turn for this database and restore it
		while exists (select * from #dir where s like @dbname + '^_%' escape '^')
		begin
			select 	top 1 @filename = s
			from	#dir
			where	s like @dbname + '^_%' escape '^'
			order by right(s, 20)
			
if @Debug <> 0
select filename = @filename
			
			if @filename like '%^_Full^_%' escape '^'
			begin
				-- restore a full backup
				if exists (select * from master..sysdatabases where name = @dbname)
				begin
					select @cmd = 'drop database ' + @dbname
					exec (@cmd)
				end
				
				select @cmd = 'restore filelistonly  from disk = ''' + @SourcePath + @filename + ''''
				delete 	#files	
				insert #files exec (@cmd)
				
				-- now buld the restore
				select 	@cmd = null ,
					@s = ''
				
				while @s < (select max(lname) from #files)
				begin
					select top 1 @s = lname, @t = type from #files where lname > @s order by lname
					
					select @cmd = coalesce(@cmd + ',move ', '') + '''' + @s 
							+ ''' to ''' + case when @t = 'D' then @DataPath else @LogPath end
							+ @s + ''''
				end
				select @cmd = 'restore database ' + @dbname + ' from disk = ''' + @SourcePath + @filename + ''' with move ' + @cmd + ', NORECOVERY'		-- + ', standby = ''' + @localpath + 'standby.fil'''
				exec (@cmd)
			end
			else if @filename like '%^Log^_%' escape '^'
			begin
				-- restore a log backup
				select @cmd = 'restore log ' + @dbname + ' from disk = ''' + @SourcePath + @filename + ''' with NORECOVERY'
				exec (@cmd)
			end
			
			-- move to archive
			select @cmd = 'move ' + @SourcePath + @filename + ' ' + @archivePath + @filename
			exec master..xp_cmdshell @cmd
			
			delete	#dir where s = @filename
		end
		
		-- now set to correct recovey mode
		if @recover = 'recover'
		begin
			select @cmd = 'restore database ' + @dbname + ' with recovery'
			exec (@cmd)
		end
	end

GO