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.
.gif "Security note") 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:
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.
Restore the most recent full database backup without recovering the database (RESTORE DATABASE database_name FROM backup_device WITH NORECOVERY).
If differential backups exist, restore the most recent one without recovering the database (RESTORE DATABASE database_name FROM differential_backup_device WITH NORECOVERY).
Starting with the first transaction log backup that was created after the backup you just restored, restore the logs in sequence with NORECOVERY.
Recover the database (RESTORE DATABASE database_name WITH RECOVERY). Alternatively, this step can be combined with restoring the last log backup.
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.
The following illustration shows this process. After a failure occurs (1), a tail-log backup is created (2). Next, the database is restored to the point of the failure. This involves restoring a database backup, a subsequent differential backup, and every log backup taken after the differential backup, including the tail-log backup.
.gif "Complete database restore to the time of a failure")
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:
RESTORE DATABASE database FROM full database backup WITH NORECOVERY;
RESTORE DATABASE database FROM full_differential_backup WITH NORECOVERY;
RESTORE LOG database FROM log_backup WITH NORECOVERY;
Repeat this restore-log step for each additional log backup.
RESTORE DATABASE database WITH RECOVERY;
Example
For the following example, the AdventureWorks2008R2 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 AdventureWorks2008R2 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;
--Create tail-log backup.
BACKUP LOG AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2FullRM.bak'
WITH NORECOVERY;
GO
--Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2FullRM.bak'
WITH FILE=1,
NORECOVERY;
--Restore the regular log backup (from backup set 2).
RESTORE LOG AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2FullRM.bak'
WITH FILE=2,
NORECOVERY;
--Restore the tail-log backup (from backup set 3).
RESTORE LOG AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2FullRM.bak'
WITH FILE=3,
NORECOVERY;
GO
--recover the database:
RESTORE DATABASE AdventureWorks2008R2 WITH RECOVERY;
GO
Recovering to the Point of Failure
To restore a full database backup
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)
Important
When you restore a database backup onto a different server instance, see Copying Databases with Backup and Restore and Managing Metadata When Making a Database Available on Another Server Instance.
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 recover the database.
To restore a backup by using SQL Server Management Objects (SMO)
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 2008, you can restore a database backup that was created by using SQL Server 2000, SQL Server 2005, or SQL Server 2008. However, backups of master, model and msdb that were created by using SQL Server 2000 or SQL Server 2005 cannot be restored by SQL Server 2008. Also, SQL Server 2008 backups cannot be restored by any earlier version of SQL Server.
SQL Server 2008 uses a different default path than earlier versions. Therefore, to restore a database that was created in the default location of either SQL Server 2000 or SQL Server 2005 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.
Note
For information about how to upgrade a database that was created by using SQL Server version 7.0 or earlier to SQL Server 2005, see Copying Databases from SQL Server 7.0 or Earlier.
See Also