Performing a Complete Database Restore (Simple Recovery Model)

  • Article

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

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. 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.

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.

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 2005, you can restore a database backup that was created by using SQL Server version 7.0, SQL Server 2000, or SQL Server 2005. However, backups of master, model and msdb that were created by using SQL Server 7.0 or SQL Server 2000 cannot be restored by SQL Server 2005.

SQL Server 2005 uses a different default path than earlier versions. Therefore, to restore a database that was 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 were 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 was created by using SQL Server 6.5 or earlier to SQL Server 2005, see Copying Databases from SQL Server 6.5 or Earlier.

See Also

Concepts

Full Database Backups
Differential Database Backups
Backup Under the Simple Recovery Model
Reducing Recovery Time When Restoring a Database
Restore Restrictions Under the Simple Recovery Model
Understanding How Restore and Recovery of Backups Work in SQL Server

Other Resources

RESTORE (Transact-SQL)
BACKUP (Transact-SQL)
sp_addumpdevice (Transact-SQL)
Working with Restore Sequences for SQL Server Databases

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
New content:
  • Provided the basic RESTORE syntax in the "Restoring a Whole Database" section.
Changed content:
  • Changed the example BACKUP and RESTORE statements to specify the physical backup device by using the DISK option, instead of specifying a logical backup device.