How to apply a transaction log backup (Transact-SQL)

How to apply a transaction log backup (Transact-SQL)

It is not possible to apply a transaction log backup:

• Unless the database or differential database backup preceding the transaction log backup is restored first.
  
  
• Unless all preceding transaction logs created since the database or differential database were backed up are applied first.
  
  
• If the database has already recovered and all outstanding transactions have either been rolled back or rolled forward.

To apply a transaction log backup

1. Execute the RESTORE LOG statement to apply the transaction log backup, specifying:
   • The name of the database to which the transaction log will be applied.
     
     
   • The backup device where the transaction log backup will be restored from.
     
     
   • The NORECOVERY clause if you have another transaction log backup to apply after the current one, otherwise specify the RECOVERY clause.
2. Repeat Step 1 for each transaction log backup you need to apply.

Examples

A. Applying a single transaction log backup

This example applies a transaction log backup to the MyNwind database.

RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH RECOVERY
GO

B. Applying multiple transaction log backups

This example applies multiple transaction log backups to the MyNwind database.

RESTORE LOG MyNwind
   FROM MyNwind_log1
   WITH NORECOVERY
GO
RESTORE LOG MyNwind
   FROM MyNwind_log2
   WITH NORECOVERY
GO
RESTORE LOG MyNwind
   FROM MyNwind_log3
   WITH RECOVERY
GO

See Also

Transaction Log Backups

RESTORE

Reducing Recovery Times

File and Filegroup Backup and Restore