Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Manage and Monitor SQL Server - Lots of demos!
 

How to restore a SQL Server transaction log backup



By:

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




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.



    



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.

-Greg


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

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


Learn more about SQL Server tools