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