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”
Log file will have the extn trn and not bak….please correct the script….thanks!