Restore Database to Point of Failure - Full Recovery

Applies to: SQL Server

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

   USE master;  
   GO  
   ALTER DATABASE AdventureWorks2022 SET RECOVERY FULL;  
   

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

   BACKUP DATABASE AdventureWorks2022 TO DISK = 'C:\AdventureWorks2022_Data.bck';  
   

3. Create a routine log backup:

   BACKUP LOG AdventureWorks2022 TO DISK = 'C:\AdventureWorks2022_Log.bck';  
   

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

See Also

BACKUP (Transact-SQL)
RESTORE (Transact-SQL)