Restore Redgate Transaction logs files via SQL

-- To enable  xp_cmdshell in the server if not enabled.
exec sp_configure 'xp_cmdshell',1
reconfigure with override;
go

declare@getRGFileNames nvarchar(1000),
@rgpath nvarchar(200)

set @rgpath ='C:\DatabaseBackup\TransactionLogs\' -- Location for the Red Gate transaction path

create table #TempRGFileList
(
RGFileName varchar(400) null,
) 

select@getRGFileNames = 'xp_cmdshell ' + '''DIR /b ' + @rgpath +''''

insertinto #TempRGFileList (RGFileName)
execute(@getRGFileNames) 


create table  #TransactionRestoreDatabaseDetails
(
RowNumberintnot null,
RGFileNamenvarchar(300)not null,
createdatedatetimenot null,
ConvertFilenvarchar(2000)null,
Restorecmdnvarchar(2000)null,
Remarksnvarchar(1000)null,
Updatedatetimedatetimenull
);

insert into #TransactionRestoreDatabaseDetails
(
RowNumber,
RGFileName,
createdate,
Updatedatetime

)
selectrow_number() over (order by RGFilename desc) as 'RowNumber',
RGFilename,
getdate(),
getdate()
from#TempRGFileList
whereRGFilename is not null;


Declare @rg_TrnPathnvarchar(300),
@sql_TrnPathnvarchar(300),
@filenamenvarchar(300),
@sqlFilenamenvarchar(300),
@countint,
@scriptnvarchar(2000),
@restorecmdnvarchar(2000),
@trnFile0nvarchar(300),
@trnFile1nvarchar(300),
@trnFile2nvarchar(300),
@appPathnvarchar(100),
@databaseNamenvarchar(100)



select@rg_TrnPath= 'C:\DatabaseBackup\RG_Backup\', --Location where the Red Gate transaction logs are present.
@sql_TrnPath= 'C:\DatabaseBackup\TransactionLogs\', -- Location where the SQL Transaction logs will be extracted.
@appPath= 'C:\DatabaseBackup\sqb2mtf.exe ', -- Location for the application of to extract the Red Gate files into .trn files.
@databaseName= '[DatabaseName]'; -- name of the database for restoring transactions.



select@count = count(1) 
fromTransactionRestoreDatabaseDetails;

while(@count >=1)
begin

select@filename = RGFileName
fromTransactionRestoreDatabaseDetails
whereRowNumber = @count

select @sqlFilename = replace(@filename, 'sqb', 'trn')



select @script= 'xp_cmdshell'+ ''''+ @appPath+ ' "' + @rg_TrnPath + @filename + '"'
+ ' "'+ @sql_TrnPath + @sqlFilename +'"' +''''
 
print @script;

exec(@script)


select@trnFile0= replace(@sqlFilename, '.trn' , '_00.trn'),
@trnFile1= replace(@sqlFilename, '.trn' , '_01.trn'),
@trnFile2= replace(@sqlFilename, '.trn' , '_02.trn')

select @restorecmd= ' RESTORE LOG ' + @databaseName
+ ' FROM  DISK = N'''+@sql_TrnPath + @trnFile0 +''','
+ ' DISK = N'''+ @sql_TrnPath + @trnFile1 +''',' 
+ ' DISK = N'''+ @sql_TrnPath + @trnFile2 +''''
+ ' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10'

print @restorecmd;

exec (@restorecmd)

updateTransactionRestoreDatabaseDetails
setRemarks= 'Restore completed for ' + @trnFile0 + '; '+ @trnFile1 + '; ' + @trnFile2,
ConvertFile= @script,
Restorecmd= @restorecmd,
Updatedatetime = getdate()
whereRowNumber = @count


select @count = @count - 1

end
go