Complete Database Restores (Simple Recovery Model)
Applies to: SQL Server (all supported versions)
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.
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.
In this Topic:
For information about support for backups from earlier versions of SQL Server, see the "Compatibility Support" section of RESTORE (Transact-SQL).
Overview of Database Restore Under the Simple Recovery Model
A full database restore under the simple recovery model involves one or two RESTORE statements, depending on whether you want to restore a differential database backup. If you are using only a full database backup, just restore the most recent backup, as shown in the following illustration.
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.
If you plan to restore a database backup onto a different server instance, see Copy Databases with Backup and Restore.
Basic Transact-SQL RESTORE Syntax
The basic Transact-SQLRESTORE syntax for restoring a full 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.
The basic RESTORE syntax for restoring a database backup is:
RESTORE DATABASE database_name FROM backup_device WITH RECOVERY
The following example first shows how to use the BACKUP statement to create a full database backup and a differential database backup of the
AdventureWorks2019 database. The example then restores these backups in sequence. The database is restored to its state as of the time that the differential database backup finished.
The 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. When you recover a database, we recommend explicitly specifying the RECOVERY option for clarity, even though it is the default.
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 AdventureWorks2012 SET RECOVERY SIMPLE; GO -- Back up the full AdventureWorks2012 database. BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' WITH FORMAT; GO --Create a differential database backup. BACKUP DATABASE AdventureWorks2012 TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' WITH DIFFERENTIAL; GO --Restore the full database backup (from backup set 1). RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' WITH FILE=1, NORECOVERY; --Restore the differential backup (from backup set 2). RESTORE DATABASE AdventureWorks2012 FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2012.bak' WITH FILE=2, RECOVERY; GO
To restore a full database backup
To restore a differential database backup
To restore a backup by using SQL Server Management Objects (SMO)
Full Database Backups (SQL Server)
Differential Backups (SQL Server)
Backup Overview (SQL Server)
Restore and Recovery Overview (SQL Server)
Submit and view feedback for