Transactional Logs Restore

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

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


set nocount on;


Declare @Script nvarchar(1000),
@countint,
@filenamevarchar(500),
@search_frompathvarchar(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_Scriptnvarchar(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
(
Idint identity(1,1),
Datesvarchar(20),
Timesvarchar(20),
Filenamesvarchar(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
)
selectleft(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) 
fromTemp_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
fromTemp_FileList
whereId = @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