Technical Article

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
(
@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

Rate

Share

Share

Rate