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 +''''

	insert	into #TempRGFileList (RGFileName)
	execute	(@getRGFileNames) 

	
create table  #TransactionRestoreDatabaseDetails
(
	RowNumber		int				not null,
	RGFileName		nvarchar(300)	not null,
	createdate		datetime		not null,
	ConvertFile		nvarchar(2000)		null,
	Restorecmd		nvarchar(2000)		null,
	Remarks			nvarchar(1000)		null,
	Updatedatetime	datetime			null
);

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

	)
select	row_number() over (order by RGFilename desc) as 'RowNumber',
		RGFilename,
		getdate(),
		getdate()
from	#TempRGFileList
where	RGFilename is not null;


Declare @rg_TrnPath		nvarchar(300),
		@sql_TrnPath	nvarchar(300),
		@filename		nvarchar(300),
		@sqlFilename	nvarchar(300),
		@count			int,
		@script			nvarchar(2000),
		@restorecmd		nvarchar(2000),
		@trnFile0		nvarchar(300),
		@trnFile1		nvarchar(300),
		@trnFile2		nvarchar(300),
		@appPath		nvarchar(100),
		@databaseName	nvarchar(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) 
from	TransactionRestoreDatabaseDetails;

while(@count >=1)
begin

	select	@filename = RGFileName
	from	TransactionRestoreDatabaseDetails
	where	RowNumber = @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)

	update	TransactionRestoreDatabaseDetails
	set		Remarks		= 'Restore completed for ' + @trnFile0 + '; '+ @trnFile1 + '; ' + @trnFile2,
			ConvertFile	= @script,
			Restorecmd	= @restorecmd,
			Updatedatetime = getdate()
	where	RowNumber = @count


	select @count = @count - 1
	
end
go