Restore a Database to the Point of Failure Under the Full Recovery Model (Transact-SQL)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
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
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;
Restore a full database backup by running the following basic RESTORE DATABASE statement:
Optionally, restore a differential database backup by running the following basic RESTORE DATABASE statement:
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;
Recover the database by running the following RESTORE DATABASE statement:
RESTORE DATABASE <database_name> WITH RECOVERY;
Before you can run the example, you must complete the following preparations:
The default recovery model of the AdventureWorks2012 database is the simple recovery model. Because this recovery model does not support restoring to the point of a failure, set AdventureWorks2012 to use the full recovery model by running the following ALTER DATABASE statement:
USE master; GO ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
Create a full database back of the database by using the following BACKUP statement:
BACKUP DATABASE AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Data.bck';
Create a routine log backup:
BACKUP LOG AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Log.bck';
The following example restores the backups that are created previously, after creating a tail-log backup of the AdventureWorks2012 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.
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 AdventureWorks2012 TO DISK = 'C:\AdventureWorks2012_Log.bck' WITH NORECOVERY; GO -- Step 2: Restore the full database backup. RESTORE DATABASE AdventureWorks2012 FROM DISK = 'C:\AdventureWorks2012_Data.bck' WITH NORECOVERY; GO -- Step 3: Restore the first transaction log backup. RESTORE LOG AdventureWorks2012 FROM DISK = 'C:\AdventureWorks2012_Log.bck' WITH NORECOVERY; GO -- Step 4: Restore the tail-log backup. RESTORE LOG AdventureWorks2012 FROM DISK = 'C:\AdventureWorks2012_Log.bck' WITH NORECOVERY; GO -- Step 5: Recover the database. RESTORE DATABASE AdventureWorks2012 WITH RECOVERY; GO