Create SQL Script to restore Transaction logs

I was part of a Major incident this week and we had to restore days worth of transaction logs on multiple databases.

Created SQL Query which generates the restore script

-- This Query generates SQL script to restore logs from one server to other during mirroring or always on or any other HA configuration

DECLARE @myStartTime datetime
SET @myStartTime = dateadd(dd,-2,getdate())

SELECT top 100 'restore LOG [' + database_name + '] FROM DISK =  ''\\' + convert(varchar(80),SERVERPROPERTY('MachineName')) + '\'+  replace(physical_device_name,':','$') + ''' with NORECOVERY' as Query
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
where database_name in ('myDatabaseName')
and type = 'L' -- Transaction log backups 
and backup_finish_date > @myStartTime
ORDER BY backup_finish_date

This entry was posted in Script, SQL, SQL Query, SQL Server, Uncategorized and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s