Share via

  • 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 AdventureWorks database is the simple recovery model. Because this recovery model does not support restoring to the point of a failure, set AdventureWorks to use the full recovery model by running the following ALTER DATABASE statement:

    USE master;
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL;

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

    BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks_Data.bck';

  3. Create a routine log backup:

    BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks_Log.bck';

The following example restores the backups that are created previously, after creating a tail-log backup of the AdventureWorks 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 AdventureWorks
   TO DISK = 'C:\AdventureWorks_Log.bck'
   WITH NORECOVERY;
GO
-- Step 2: Restore the full database backup.
RESTORE DATABASE AdventureWorks
   FROM DISK = 'C:\AdventureWorks_Data.bck'
   WITH NORECOVERY;
GO
-- Step 3: Restore the first transaction log backup.
RESTORE LOG AdventureWorks
   FROM DISK = 'C:\AdventureWorks_Log.bck'
   WITH NORECOVERY;
GO
-- Step 4: Restore the tail-log backup.
RESTORE LOG AdventureWorks
   FROM  DISK = 'C:\AdventureWorks_Log.bck'
   WITH NORECOVERY;
GO
-- Step 5: Recover the database.
RESTORE DATABASE AdventureWorks
   WITH RECOVERY;
GO