مشاركة عبر

Complete Database Restores (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.

Security note Security Note

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:

  • Overview of Database Restore Under the Simple Recovery Model

  • Related Tasks

Note

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.

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

Note

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-SQL RESTORE syntax for restoring a full 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.

The basic RESTORE syntax for restoring a database backup is:

RESTORE DATABASE database_name FROM backup_device WITH RECOVERY

Example (Transact-SQL)

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.

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

[Top]

To restore a full database backup

To restore a differential database backup

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

[Top]

See Also

Reference

RESTORE (Transact-SQL)

BACKUP (Transact-SQL)

sp_addumpdevice (Transact-SQL)

Concepts

Full Database Backups (SQL Server)

Differential Backups (SQL Server)

Backup Overview (SQL Server)

Restore and Recovery Overview (SQL Server)