Transactional Logs Restore

if object_id('DBA_Restore_TransactionLog','P') is not null
	drop procedure DBA_Restore_TransactionLog
go

create procedure DBA_Restore_TransactionLog
(
	@database	varchar(200), -- Database Name 
	@from_path	varchar(1000) -- Path from where the transaction files are. 
)
as
begin


	set nocount on;


	Declare @Script 		nvarchar(1000),
		@count			int,
		@filename		varchar(500),
		@search_frompath	varchar(1000)



-- Temp Table to get the results into the temp table. 
	if object_id('tempdb..#Temp_CheckExistenceFolder', 'U') is not null
		drop table #Temp_CheckExistenceFolder

		create table #Temp_CheckExistenceFolder
		(
			id int identity(1,1),
			Output_Results varchar(2000)
		)

	

--Temp table to store the execution script for the transaction logs applied.
	if object_id('Temp_ExecuteFileList','U') is not null
		drop table Temp_ExecuteFileList

	create table Temp_ExecuteFileList
	(
		Exec_Script	nvarchar(1500)
	)


-- Temp table to store the File List with date and time got from the dir command. The table helps in to store the transaction log 
-- file details to be applied on the database. 
	if object_id('Temp_FileList', 'U') is not null
		drop table Temp_FileList

	create table Temp_FileList
	(
		Id		int identity(1,1),
		Dates		varchar(20),
		Times		varchar(20),
		Filenames	varchar(500)
	)


--The DIR command to take the file list for the transaction file.
	select @script = 'exec master..xp_cmdshell ''dir '+@from_path+'/T  '''


--The output of the DIR commad is inserted into the table. 
	insert into #Temp_CheckExistenceFolder
	(
		Output_Results
	)
	exec  (@script)


-- The variable is used to delete a line from the table #Temp_CheckExistenceFolder
	select @search_frompath = '%'+@from_path +'%'

/************ Delte statements from the table #Temp_CheckExistenceFolder unwanted data generated from the DIR command. *********************/

-- Delete any rows which had values as Null
	delete #Temp_CheckExistenceFolder where Output_Results is null

	
-- Delete any rows which has values as %volume%'
	delete #Temp_CheckExistenceFolder where Output_Results  like '%Volume%'

-- Delete any rows which has values as <DIR>
	delete #Temp_CheckExistenceFolder where Output_Results  like '%<Dir>%'

-- Delete any rows which provides count of files persent in the path
	delete #Temp_CheckExistenceFolder where Output_Results  like '%file(s)%'

-- Delete any rows which provides count of folders persent in the path
	delete #Temp_CheckExistenceFolder where Output_Results  like '%Dir(s)%'


-- Delete any rows which provides from what path result is from.
	delete #Temp_CheckExistenceFolder where Output_Results  like @search_frompath


-- Get the total Files list into the FileList table with columns with Date, Time and Filename. 
	insert into Temp_FileList
	(
		Dates,
		Times,
		Filenames
	)
	select	left(output_results, 10) as 'Dates' , -- Get the date from the row.
		ltrim(rtrim(substring(output_results, len(left(output_results, 13)),6))), -- Get the Time from the row.
		ltrim(rtrim(right(output_results,NULLIF(charindex(' ', REVERSE(output_results)),0))))  as 'Filenames' -- get the filenames from the row. 
	from #Temp_CheckExistenceFolder
	order by 2 desc


-- Take the total File count for the transaction log.
	select	@count = count(1) 
	from	Temp_FileList


-- While loop to apply the transaction log. 

	while(@count >=1)
	begin

		-- Take the file names from the table to be applied.
		select	@filename = Filenames
		from	Temp_FileList
		where	Id = @count

		-- Generate the script for transaction log with from path and filename. 
		select @script	= ' RESTORE LOG '+ @database 
				+ '   FROM disk = '''+@from_path +'\'+@filename +''''  
				+ '    WITH NORECOVERY;' 


		-- The execution script is inserted into the table to debug if any problems. 
		insert into Temp_ExecuteFileList
			(
				Exec_Script
			)
		select @script


		-- Execute the actual script to apply the transaction log. 
		exec(@script)
		if(@@error<> 0)
		begin
		
			select @script
		end
		

		-- @Count variable gets values for the next run for the while loop. 
		select @count = @count -1 
	end

end
go