Transactional Logs Restore

,

As a DBA, we have to test our backups with restore testing. 
To test the restore manually it does becomes a bit of lengthy process. 
To ease out the process of copy and pasting all the files what we want to test the restore the procedure will help.
Requirments:.
1. Please restore the database and leave the database in non-recovery mode. 
2. Copy all the transaction log files into one folder location and make sure that the service account has correct permission to read and write the files. 
3. execute the procedure as below.
    DBA_Restore_TransactionLog 'DatabaseName', 'S:\backup'
If error happens like below 
Server: Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 2413977000000004100001, which is too late to apply to the database. An earlier log backup that includes LSN 2413630000000004100001 can be restored.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
To know what files were picked up use the table Temp_FileList
Id          Dates                Times                Filenames                                                                                                                                                                                                                                                        
----------- -------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           23/06/2018           08:00                DBName_20180623_080002.trn
2           23/06/2018           07:19                DBName_20180623_071904.trn
3           23/06/2018           07:18                DBName_20180623_071442.trn
4           23/06/2018           07:06                DBName_20180623_070609.trn
5           23/06/2018           07:06                DBName_20180623_070613.trn
6           23/06/2018           07:01                DBName_20180623_070007.trn
7           23/06/2018           06:00                DBName_20180623_060003.trn
8           23/06/2018           05:00                DBName_20180623_050002.trn
9           23/06/2018           04:00                DBName_20180623_040003.trn

To see what the procedure has executed, use the table Temp_ExecuteFileList
select * from Temp_ExecuteFileList
Exec_Script                                                                                                                                                                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_000002.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_010003.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_020002.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_030002.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_040003.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_050002.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_060003.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_070007.trn'    WITH NORECOVERY;
 RESTORE LOG Vectus_stoke   FROM disk = 'F:\backup\DBName_20180623_070613.trn'    WITH NORECOVERY;
 

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

Rate

Share

Share

Rate