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

To apply a transaction log backup

Important The system administrator restoring the transaction log must be the only person currently using the database where the transaction log will be applied.

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.

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