Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


How to restore a SQL Server transaction log backup



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. 


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.


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'

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
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks_2.TRN'

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
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH NORECOVERY, FILE = 2
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH FILE = 3

Last Update: 3/17/2009

More SQL Server Solutions

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Friday, February 15, 2019 - 1:17:16 AM - Tanveer Back To Top

Possible schema corruption error showing while restoring Databse , can u suggest some solution???

Tuesday, August 09, 2016 - 10:08:05 AM - Greg Robidoux Back To Top

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.


Tuesday, August 09, 2016 - 9:12:22 AM - Mike Curtis Back To Top

Hi, could you double-check:

RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.DIF'




Thank you,


Learn more about SQL Server tools