Restore transaction log with norecovery in SQL Server

Restore transaction log with NORECOVERY option is used when restoring multiple transaction log. Consider a backup strategy where in full backup is being taken once a week and transaction log backup is taken every 3 hrs. In this case if we have to restore a database, we have to restore multiple transaction log backups. Also, a transaction log backup is restored over a full/differential backup.

Suppose we have 3 T-log backups to restore then we’ll do it as shown below.

RESTORE LOG Adventureworks FROM DISK='E:\Ahmad\Adventureworks_1_log.bak' WITH NORECOVERY

RESTORE LOG Adventureworks FROM DISK='E:\Ahmad\Adventureworks_2_log.bak' WITH NORECOVERY

RESTORE LOG Adventureworks FROM DISK='E:\Ahmad\Adventureworks_3_log.bak' WITH RECOVERY

The first two RESTORE statement use NORECOVERY option leaving database in restoring state so as to restore more T-log backups. The database isn’t accessible in recovery state. The last query uses RECOVERY option to recover the database by redo/undo the transactions from the log backup.

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

One Comment on “Restore transaction log with norecovery in SQL Server”

Leave a Reply

Your email address will not be published. Required fields are marked *