Generate SQL Script to Restore Multiple Transaction Logs

Posted by Barac in Powershell, SQL Server, SQL Tips and Tricks on Feb 12th, 2019 | 0 comments

Restore multiple TRN logs using PowerShell. In this example I will show you really simple way how you can use PowerShell to restore multiple logs at once.

After restoring full backup WITH NORECOVERY you need to figure it out last log sequence number of the last transaction in the backup set. TRN Log backups must be restored in the order in which they were created. Log backups contain log sequence numbers (LSN), and they must be restored in that particular order. There are few ways to find last LSN.

USE msdb
GO
SELECT  TOP 10
bck.database_name
, bck.first_lsn
, bck.last_lsn
, bck.backup_start_date
, bck.backup_finish_date
, bck.type
FROM backupset bck 
WHERE bck.database_name = 'db_name' AND bck.type='D'
ORDER BY bck.backup_finish_date DESC

Backup type can be:
D = Database
I = Differential database
L = Log
F = File or filegroup
G =Differential file
P = Partial
Q = Differential partial
Can be NUL

Backupset table is stored in the MSDB database. If you want to read more about you can find additional information on the following MS article.

https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupset-transact-sql?view=sql-server-2017

Choose database you want to apply logs and you should get something similar to following picture:

Lets start with PowerShell script.

First you need to put all TRN Logs to one folder and then just run following PS script

$list = get-childitem -Path D:\Temp\tlog
$list | ForEach-Object {"RESTORE LOG appsdata FROM DISK = '$($_.FullName)' WITH NORECOVERY;"}

You should get something like following

That is all, just copy your results and run using your SSMS. To recover the database add WITH RECOVERY to the last RESTORE LOG statement

RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_000010.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_001009.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_002011.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_003010.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_004010.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_005010.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_010009.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_011010.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_012009.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_013011.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_014008.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_015013.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_020036.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_021040.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_022033.trn' WITH NORECOVERY;
RESTORE LOG appsdata FROM DISK = 'C:\Temp\tlog\testdata_backup_2019_07_17_023033.trn' WITH RECOVERY;

About Me

My name is Zoran, currently living in Auckland, New Zealand. I am a data specialist with more than 15 years of hands-on experience in database administration and optimisation. I am also, Certified Microsoft Trainer (MCT) and Microsoft Certified Solutions Expert (MCSE) with a Master‘s degree in Information Technology. Sharing knowledge and contributing to the SQL Server community is my passion. I am also an organiser of the Auckland SQL User Meetup Group. As well I am active blogger and speaker at different SQL events such as SQL Saturdays, Meetups etc.

PowerShell / SQL Server / Generate SQL Script to Restore Multiple Transaction Logs

Similar Posts:

Leave a Reply Cancel reply

About Me

Email Subscription

Index Usage Report

Auckland SQL Group

MSDN PROFILE

Archives

SQL SERVER UPDATES

Recent Posts

Tag Cloud