Performing a Complete Database Restore (Full Recovery Model)

In a complete database restore, the goal is to restore the whole database. The whole database is offline for the duration of the restore. Before any part of the database can come online, all data is recovered to a consistent point in which all parts of the database are at the same point in time and no uncommitted transactions exist.

Under the full recovery model, the database can be restored to a specific point in time. The point in time can be the most recently available backup, a specific date and time, or a marked transaction.

Security Note:

We recommend that you do not attach or restore databases from unknown or untrusted sources. These databases could contain malicious code that might execute unintended Transact-SQL code or cause errors by modifying the schema or the physical database structure. Before you use a database from an unknown or untrusted source, run DBCC CHECKDB on the database on a nonproduction server and also examine the code, such as stored procedures or other user-defined code, in the database.

Restoring a Complete Database

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.

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

To restore a full database backup

• How to: Restore a Database Backup (Transact-SQL)
• How to: Restore a Database Backup (SQL Server Management Studio)
• How to: Create a New Database From an Existing Database Backup (SQL Server Management Studio)

To restore a differential database backup

• How to: Restore a Differential Database Backup (Transact-SQL)
• How to: Restore a Differential Database Backup (SQL Server Management Studio)

To restore a transaction log backup

After you restore your data backup or backups, you must restore all of the later transaction log backups and then recovery the database.

• How to: Apply a Transaction Log Backup (Transact-SQL)
• How to: Restore a Transaction Log Backup (SQL Server Management Studio)

To restore a backup by using SQL Server Management Objects (SMO)

• SqlRestore

Recovering to a Point-in-Time

Under the full recovery model, a database can be restored to a specific point in time within a log backup. The point in time can be a specific date and time, marked transaction, or log sequence number (LSN). For more information, see Restoring a Database to a Point Within a Backup.

Support for Backups from Earlier Versions of SQL Server

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.

See Also

Concepts

Backup Under the Bulk-Logged Recovery Model
Backup Under the Full Recovery Model
Differential Database Backups
Full Database Backups
Reducing Recovery Time When Restoring a Database
Understanding How Restore and Recovery of Backups Work in SQL Server
Working with Transaction Log Backups

Other Resources

Working with Restore Sequences for SQL Server Databases

Help and Information

Getting SQL Server 2005 Assistance

Change History