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
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. .
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.
The example starts with an ALTER DATABASE statement that sets the recovery model to
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 ]
Use WITH NORECOVERY if you plan to also restore a differential 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
The basic RESTORE syntax for restoring a differential database backup is:
RESTORE DATABASE database_name FROM backup_device WITH RECOVERY
- How to: Restore a Differential Database Backup (Transact-SQL)
- How to: Restore a Differential Database Backup (SQL Server Management Studio)
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.
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.
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
Help and Information
17 July 2006