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

  • Makale

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 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

See Also

Tasks

How to: Restore a Transaction Log Backup (SQL Server Management Studio)
How to: Restore to a Point in Time (SQL Server Management Studio)
How to: Create a Transaction Log Backup (Transact-SQL)
How to: Restore a Database to a Marked Transaction (SQL Server Management Studio)
How to: Restore to the Point of Failure (Transact-SQL)
How to: Restore to a Point in Time (Transact-SQL)

Concepts

Working with Transaction Log Backups

Other Resources

RESTORE (Transact-SQL)
SQL Server Management Studio Tutorial

Help and Information

Getting SQL Server 2005 Assistance