How to restore a SQL Server transaction log backup

Overview

The RESTORE LOG command allows you to restore a transaction log backup. The options include restoring the entire transaction log or to a certain point in time or to a certain transaction mark. In order to restore a transaction log backup, the database has to be in a restoring state which means that a full backup or a full backup and differential backup were first restored using the NORECOVERY option to allow additional backups, like transaction log backups to be restored.

Explanation

When restoring a transaction log you will need exclusive access to the database, which means no other user connections can be using the database. If the database is in a restoring state this is not an issue, because no one can be using the database.

The RESTORE LOG option can be done using either T-SQL or using SQL Server Management Studio.

T-SQL

Restore a transaction log backup
To restore a transaction log backup the database need to be in a restoring state. This means that you would have to restore a full backup and possibly a differential backup as well.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
GO

Restore multiple transaction log files (NORECOVERY)
The NORECOVERY option leaves the database in a restoring state after the restore has completed. This allows you to restore additional files to get the database more current. By default this option is turned off. As was mentioned above the database needs to be in a restoring state, so this would have already been done for at least one backup file that was restored.

This shows restoring two transaction log backups, the first using NORECOVERY and the second statement does not which means the database will be accessible after the restore completes.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_1.TRN' WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'
GO

Restore multiple transaction log backups from the same backup file
Let's say we use the same backup file, AdventureWorks.TRN, to write all of our transaction log backups. This is not a best practice, because if the file is corrupt then this could corrupt all of your backups in this file. We can use RESTORE HEADERONLY to see the backups and the positions in the backup file. Let's say that the restore headeronly tells us that we have 3 transaction log backups in this file and we want to restore all three. The restore commands would be.

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 1
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 2
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH FILE = 3
GO

Last Update: 3/17/2009

<< Previous

Next >>

Comments For This Article

Friday, February 15, 2019 - 1:17:16 AM - Tanveer	Back To Top (79038)
Possible schema corruption error showing while restoring Databse , can u suggest some solution???

Tuesday, August 9, 2016 - 10:08:05 AM - Greg Robidoux

Hi Mike,

You are correct. That section was carried over from the RESTORE DATABASE section and shouldn't have been included here.

Thanks for pointing that out. That section has been removed as it does not apply.

-Greg

Tuesday, August 9, 2016 - 9:12:22 AM - Mike Curtis

Hi, could you double-check:

https://www.mssqltips.com/sqlservertutorial/111/how-to-restore-a-sql-server-transaction-log-backup/
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'
GO

Isn't it RESTORE DATABASE?

Thank you,

Mike