Share via

  • Article

Performing a Complete Database Restore (Simple 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 simple recovery model, the database cannot be restored to a specific point in time within a specific backup.

Restoring a Whole Database

  1. A full database restore under the simple recovery model involves only one or two RESTORE statements, depending on whether a differential database backup has to be restored.

If you are using only a full database backup, just restore the most recent backup, as shown in the following illustration.

Restoring only a full database backup

If you are also using a differential database backup, restore the most recent full database backup without recovering the database, and then restore the most recent differential database backup and recover the database. The following illustration shows this process.

Restoring full and differential database backups

When you are completely restoring a database, one restore sequence should be used. The following example shows the critical options in a restore sequence for the complete database restore scenario. 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 to its state as of a full database backup. When you recover a database, we recommend explicitly specifying the RECOVERY option for clarity, even though it is the default.

Example

The following example first shows how to use the BACKUP statement to create a full database backup and a differential database backup of the AdventureWorks database. The example then restores these backups in sequence.

Note

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

USE master;
--Make sure the database is using the simple recovery model.
ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE;
GO
-- Back up the full AdventureWorks database.
BACKUP DATABASE AdventureWorks TO DISK = 'Z:\SQLServerBackups\AdventureWorks.bak' 
  WITH FORMAT;
GO
--Create a differential database backup.
BACKUP DATABASE AdventureWorks TO DISK = 'Z:\SQLServerBackups\AdventureWorks.bak'
   WITH DIFFERENTIAL;
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 differential backup (from backup set 2).
RESTORE DATABASE AdventureWorks FROM DISK = 'Z:\SQLServerBackups\AdventureWorks.bak' 
   WITH FILE=2, RECOVERY;
GO

To restore a full database backup

The basic RESTORE syntax for restoring a database backup is:

RESTORE DATABASE database_name FROM backup_device [ WITH NORECOVERY ]

Note

Use WITH NORECOVERY if you plan to also restore a differential database backup.

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

The basic RESTORE syntax for restoring a differential database backup is:

RESTORE DATABASE database_name FROM backup_device WITH RECOVERY

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

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.