Thursday, May 25, 2017

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.

No comments:

Post a Comment

How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...