Backup and Restore are the common tasks that a DBA needs to perform almost daily. In this blog I’ll talk about how to restore a transaction log in SQL Server.

A transaction log backup can only be restored when a database is in restoring database. That means a full backup or a differential backup has been restored with NO RECOVERY option.  A T-SQL script to restore transaction log backup is shown below.

The below error occurs if you try to restore transaction log on a recovered/online database.

restore transaction log sql server

A T-SQL script to restore a single transaction log backup is given below.

A T-SQL script to restore multiple transaction log backup is given below.

When restoring multiple T-Log files the log files are restored in sequence the backup was taken with NORECOVERY option so as to allow restoring other log backups and the last backup is restored with RECOVERY option to recover the database and bring it online.

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook