Typically, recovering a database to the point of failure involves the following basic steps:

1. Back up the active transaction log (known as the tail of the log). This creates a tail-log backup. If the active transaction log is unavailable, all transactions in that part of the log are lost.
   

   Important:
   Under the bulk-logged recovery model, backing up any log that contains bulk-logged operations requires access to all data files in the database. If the data files cannot be accessed, the transaction log cannot be backed up. In that case, you have to manually redo all changes that were made since the most recent log backup.

   For more information, see Tail-Log Backups.
   
2. Restore the most recent full database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
   
3. If differential backups exist, restore the most recent one without recovering the database (RESTORE DATABASE database_name WITH NORECOVERY).
   
4. Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence with NORECOVERY.
   
5. Recover the database (RESTORE DATABASE database_name WITH RECOVERY). Alternatively, this step can be combined with restoring the last log backup.
   
6. A complete database restore can usually be recovered to a point of time or marked transaction within a log backup. However, under the bulk-logged recovery model, if the log backup contains bulk-logged changes, point-in-time recovery is not possible. For more information, see Restoring a Database to a Point Within a Backup.
   

When you are completely restoring a database, a single restore sequence should be used. The following example shows the critical options in a restore sequence for the complete database restore scenario in which the database is restored to the point of failure. A restore sequence consists of one or more restore operations that move data through one or more of the phases of restore. Syntax and details that are not relevant to this purpose are omitted.

The database is restored and rolled forward. A database differential is used to reduce roll-forward time. This restore sequence is intended to eliminate work loss; the last backup that is restored is a tail-log backup.

The basic RESTORE syntax for the restore sequence is:

1. RESTORE DATABASE database FROM full database backup WITH NORECOVERY;
   
2. RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY;
   
3. RESTORE LOG database FROM log_backup WITH NORECOVERY;
   Repeat this restore-log step for each additional log backup.
   
4. RESTORE DATABASE database WITH RECOVERY;
   

Example

For the following example, the AdventureWorks sample database was set to use the full recovery model before the database was backed up. The example creates a tail-log backup of the AdventureWorks database. Next, the example restores an earlier full database backup and log backup, and then the example restores the tail-log backup. The example recovers the database in a separate, final step.

Note:
This example uses a database backup and log backup that is created in the "Using Database Backups Under the Full Recovery Model" section in Full Database Backups.

The example starts with an ALTER DATABASE statement that sets the recovery model to FULL.

USE master;
--Make sure the database is using the full recovery model.
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO
--Create tail-log backup.
BACKUP LOG AdventureWorks 
TO DISK = 'Z:\SQLServerBackups\AdventureWorks.bak''Z:\SQLServerBackups\AdventureWorks.bak' 
   WITH NORECOVERY; GO
--Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks 
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak' 
  WITH FILE=1, 
    NORECOVERY;

--Restore the regular log backup (from backup set 2).
RESTORE LOG AdventureWorks 
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak' 
  WITH FILE=2, 
    NORECOVERY;

--Restore the tail-log backup (from backup set 3).
RESTORE LOG AdventureWorks 
  FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
  WITH FILE=3, 
    NORECOVERY;
GO
--recover the database:
RESTORE DATABASE AdventureWorks WITH RECOVERY;
GO

Recovering to the Point of Failure

Recovering to a Point-in-Time

In SQL Server 2005, you can restore a database backup that is created by using SQL Server version 7.0, SQL Server 2000, or SQL Server 2005. However, backups of master, model and msdb that are created by using SQL Server 7.0 or SQL Server 2000 cannot be restored by SQL Server 2005. Also, SQL Server 7.0 log backups that contain create-index operations cannot be restored to SQL Server 2000 or SQL Server 2005.

SQL Server 2005 uses a different default path than earlier versions of SQL Server. Therefore, to restore a database that is created in the default location of either SQL Server 7.0 or SQL Server 2000 from backups, you must use the MOVE option. For information about the new default path, see File Locations for Default and Named Instances of SQL Server 2005.

Note:
Database backups that are created by using SQL Server 6.5 or earlier are in an incompatible format and cannot be restored in SQL Server 2005. For information about how to upgrade a database that is created by using SQL Server 6.5 or earlier to SQL Server 2005, see Copying Databases from SQL Server 6.5 or Earlier.

Concepts

Other Resources

Help and Information