Complete Database Restores (Simple Recovery Model)
Applies To: SQL Server 2016
THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel Data Warehouse
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.
In this Topic:
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.
The basic Transact-SQLRESTORE syntax for restoring a full database backup is:
RESTORE DATABASE database_name FROM backup_device [ WITH NORECOVERY ]
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 AdventureWorks2012 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.
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)