Share via

  • Article

How to: Apply a Transaction Log Backup (Transact-SQL)

To apply a transaction log backup to a database, the following must be true:

  • Before the most recent full or differential database backup was created, the database must have been using the full recovery model or bulk-logged recovery model.

  • The restore sequence must have specified WITH NORECOVERY when restoring earlier backups.

  • Log backups must be applied in the sequence in which they were created, without any gaps in the log chain. Except for the last log backup, you must use WITH NORECOVERY, as follows:

    RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY;

  • When applying the last log backup, you can do either of the following:

    • Recover the database as part of the last BACKUP LOG statement:

      RESTORE LOG <database_name> FROM <backup_device> WITH RECOVERY;
GO

    • Wait to recover the database by using a separate RESTORE DATABASE statement:

      RESTORE LOG <database_name> FROM <backup_device> WITH NORECOVERY; 
RESTORE DATABASE <database_name> WITH RECOVERY;
GO

      Waiting to recover the database gives you the opportunity to verify that you have restored all of the necessary log backups. This approach is often advisable when you are performing a point-in-time restore.

Important

We recommend that you always explicitly specify either WITH NORECOVERY or WITH RECOVERY in every RESTORE statement to eliminate ambiguity. This is particularly important when writing scripts.

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.

    The basic syntax for this statement is as follows:

    RESTORE LOG database_name FROM <backup_device> WITH NORECOVERY.

    Where database_name is the name of database and <backup_device>is the name of the device that contains the log backup being restored.

  2. Repeat step 1 for each transaction log backup you have to apply.

  3. After restoring the last backup in your restore sequence, to recover the database use the following statement:

    RESTORE database_name WITH RECOVERY

    Important

    If you are creating a mirror database, omit the recovery step. A mirror database must remain in the RESTORING state.

Example

By default, the AdventureWorks2008R2 database uses the simple recovery model. The following examples require modifying the database to use the full recovery model, as follows:

ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL

A. Applying a single transaction log backup

The following example starts by restoring the AdventureWorks2008R2 database by using a full database backup that resides on a backup device named AdventureWorks2008R2_1. The example then applies the first transaction log backup that resides on a backup device named AdventureWorks2008R2_log. Finally, the example recovers the database.

RESTORE DATABASE AdventureWorks2008R2
   FROM AdventureWorks2008R2_1
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2_log
   WITH FILE = 1,
   WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks2008R2
   WITH RECOVERY
GO

B. Applying multiple transaction log backups

The following example starts by restoring the AdventureWorks2008R2 database by using a full database backup that resides on a backup device named AdventureWorks2008R2_1. The example then applies, one by one, the first three transaction log backups that reside on a backup device named AdventureWorks2008R2_log. Finally, the example recovers the database.

RESTORE DATABASE AdventureWorks2008R2
   FROM AdventureWorks2008R2_1
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2_log
   WITH FILE = 1,
   NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2_log
   WITH FILE = 2,
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks2008R2
   FROM AdventureWorks2008R2_log
   WITH FILE = 3,
   WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks2008R2
   WITH RECOVERY
GO