Export (0) Print
Expand All
Expand Minimize

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 noteImportant

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 noteImportant

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

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

ALTER DATABASE AdventureWorks SET RECOVERY FULL

A. Applying a single transaction log backup

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

RESTORE DATABASE AdventureWorks
   FROM AdventureWorks_1
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks
   FROM AdventureWorks_log
   WITH FILE = 1,
   WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks
   WITH RECOVERY
GO

B. Applying multiple transaction log backups

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

RESTORE DATABASE AdventureWorks
   FROM AdventureWorks_1
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks
   FROM AdventureWorks_log
   WITH FILE = 1,
   NORECOVERY
GO
RESTORE LOG AdventureWorks
   FROM AdventureWorks_log
   WITH FILE = 2,
   WITH NORECOVERY
GO
RESTORE LOG AdventureWorks
   FROM AdventureWorks_log
   WITH FILE = 3,
   WITH NORECOVERY
GO
RESTORE DATABASE AdventureWorks
   WITH RECOVERY
GO

Community Additions

ADD
Show:
© 2014 Microsoft