Generating Transaction Log script

Production transaction logs are backed up every 15 or 30 mins. We have a full database backup daily. When we need to restore a database point in time. We will need to restore transaction logs. It will take time to restore each transaction log if we have more than 20 transaction log a day.

I wrote the following script to generate Transaction log restore script which I can use to restore T log faster after full backup is restored. 

--***********Read this before running the script**************************************
-- The following script generates Transaction Log restore script.
-- If database crashed , we will need to restore a full database backup and Transaction logs.
-- After restoring a full database backup, we will need to restore all the good transaction logs which are backed up after the full back up is taken. 

-- This script will generates T Log restores scripts. We copy the scripts which is generated in message window.

-- Step1 . We will need to enable xp_cmdshell to 1 by running the following script one time only . After restore is done, we need to disbale xp_cmdshell

--sp_configure xp_cmdshell,1

--go

--reconfigure

--go

-- Step 2 . We need to change 3 variable values in the script, @BP,@BP2,@DatabaseName

-- @BP and @BP2 values are the Transaction Log backup location path and both values must be the same 

-- @DatabaseName is the name of the database that you are restoring

Declare @BP varchar(max)

Declare @GF varchar(max)

Declare @sql nvarchar(max)

--************************************************************

--Change the backup path of Transaction Logs here

--************************************************************

set @BP = 'C:\BackUp'

--************************************************************

--************************************************************

--construct sql for getting files

set @GF = ''' dir '+  @BP +'''' 

set @sql = 'EXEC MASTER..XP_CMDSHELL'+ ''+ @GF + ''

--Create a staging table

CREATE TABLE GetBackupFiles (Files VARCHAR(500))

INSERT INTO GetBackupFiles

Exec sp_executesql @sql

--Cleaning up the output from file list

--delete all directories

DELETE GetBackupFiles WHERE Files LIKE '%
%'

----delete all informational messages

DELETE GetBackupFiles WHERE Files LIKE ' %'

----delete the null values

DELETE GetBackupFiles WHERE Files IS NULL

--add FileDate column

ALTER TABLE GetBackupFiles add FileDate DateTime

GO

--split dateinfo

UPDATE GetBackupFiles SET FileDate =LEFT(files,20)

--get rid of dateinfo

UPDATE GetBackupFiles SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading spaces

UPDATE GetBackupFiles SET files =LTRIM(files)

Declare @Databasename varchar(200)

Declare  @BP2 as varchar(max)

--*************************************************************

--Change the backup path here again same backup path as @BP

--Change the Databaes name here

--*************************************************************

set @BP2 = 'C:\BackUp'

Set @Databasename = 'MyDatabase'

--**************************************************************

--**************************************************************

-- Generate Restore script for Transaction Log

Declare @FN varchar(max)

Declare GetFileNames Cursor LOCAL for

--************************************************************************

--************************************************************************

-- Here we change the timestamp of the full database backup***************

--************************************************************************

--************************************************************************

select RIGHT(files,LEN(files) -PATINDEX('% %',files)) from GetBackupFiles

where FileDate >= '2017-05-24 23:45:00.000'

order by FileDate asc

--************************************************************************

--************************************************************************

Open GetFileNames;

Fetch GetFileNames into

@FN

WHILE @@FETCH_STATUS = 0

Begin

----Build Transaction Logs restore script

Print 'RESTORE DATABASE ' + @Databasename + ' FROM DISK = '''+ @BP2+@FN+' '+ ''' WITH NORECOVERY'

Fetch GetFileNames into

@FN

End

Close GetFileNames

Deallocate GetFileNames

--Drop staging table

Drop table GetBackupFiles

I gave credit to http://www.tek-tips.com/viewthread.cfm?qid=1278283

Thank you for reading.