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.

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.


Ahmad Osama

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

Follow me on TwitterFollow me on FaceBook