• Article

How to: Restore to the Point of Failure (Transact-SQL)

This topic explains how to restore to the point of failure. The topic is relevant only for databases that are using the full or bulk-logged recovery models.

To restore to the point of failure

  1. Back up the tail of the log by running the following basic BACKUP statement:

    BACKUP LOG <database_name>TO <backup_device> 
   WITH NORECOVERY, NO_TRUNCATE;

  2. Restore a full database backup by running the following basic RESTORE DATABASE statement:

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

  3. Optionally, restore a differential database backup by running the following basic RESTORE DATABASE statement:

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

  4. Apply each transaction log, including the tail-log backup you created in step 1, by specifying WITH NORECOVERY in the RESTORE LOG statement:

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

  5. Recover the database by running the following RESTORE DATABASE statement:

    RESTORE DATABASE <database_name> 
   WITH RECOVERY;

Example

Before you can run the example, you must complete the following preparations:

  1. The default recovery model of the AdventureWorks2008R2 database is the simple recovery model. Because this recovery model does not support restoring to the point of a failure, set AdventureWorks2008R2 to use the full recovery model by running the following ALTER DATABASE statement:

    USE master;
GO
ALTER DATABASE AdventureWorks2008R2 SET RECOVERY FULL;

  2. Create a full database back of the database by using the following BACKUP statement:

    BACKUP DATABASE AdventureWork2008R2s TO DISK = 'C:\AdventureWorks2008R2_Data.bck';

  3. Create a routine log backup:

    BACKUP LOG AdventureWorks2008R2 TO DISK = 'C:\AdventureWorks2008R2_Log.bck';

The following example restores the backups that are created previously, after creating a tail-log backup of the AdventureWorks2008R2 database. (This step assumes that the log disk can be accessed.)

First, the example creates a tail-log backup of the database that captures the active log and leaves the database in the Restoring state. Then, the example restores the database backup, applies the routine log backup created previously, and applies the tail-log backup. Finally, the example recovers the database in a separate step.

Note

The default behavior is to recover a database as part of the statement that restores the final backup.

/* Example of restoring a to the point of failure */
-- Step 1: Create a tail-log backup by using WITH NORECOVERY.
BACKUP LOG AdventureWorks2008R2
   TO DISK = 'C:\AdventureWorks2008R2_Log.bck'
   WITH NORECOVERY;
GO
-- Step 2: Restore the full database backup.
RESTORE DATABASE AdventureWorks2008R2
   FROM DISK = 'C:\AdventureWorks2008R2_Data.bck'
   WITH NORECOVERY;
GO
-- Step 3: Restore the first transaction log backup.
RESTORE LOG AdventureWorks2008R2
   FROM DISK = 'C:\AdventureWorks2008R2_Log.bck'
   WITH NORECOVERY;
GO
-- Step 4: Restore the tail-log backup.
RESTORE LOG AdventureWorks2008R2
   FROM  DISK = 'C:\AdventureWorks2008R2_Log.bck'
   WITH NORECOVERY;
GO
-- Step 5: Recover the database.
RESTORE DATABASE AdventureWorks2008R2
   WITH RECOVERY;
GO