Share via

Restore and Recovery Overview (SQL Server)

  • Article

To recover a SQL Server database from a failure, a database administrator has to restore a set of SQL Server backups in a logically correct and meaningful restore sequence. SQL Server restore and recovery supports restoring data from backups of a whole database, a data file, or a data page, as follows:

  • The database (a complete database restore)

    The whole database is restored and recovered, and the database is offline for the duration of the restore and recovery operations.

  • The data file (a file restore)

    A data file or a set of files is restored and recovered. During a file restore, the filegroups that contain the files are automatically offline for the duration of the restore. Any attempt to access an offline filegroup causes an error.

  • The data page (a page restore)

    Under the full recovery model or bulk-logged recovery model, you can restore individual databases. Page restores can be performed on any database, regardless of the number of filegroups.

SQL Server backup and restore work across all supported operating systems, whether they are 64-bit or 32-bit systems. For information about the supported operating systems, see Hardware and Software Requirements for Installing SQL Server 2012. For information about support for backups from earlier versions of SQL Server, see the "Compatibility Support" section of RESTORE (Transact-SQL).

In this Topic:

  • Overview of Restore Scenarios

  • Recovery Models and Supported Restore Operations

  • Restore Restrictions Under the Simple Recovery Model

  • Restore Under the Bulk-Logged Recovery Model

  • Database Recovery Advisor (SQL Server Management Studio)

  • Related Tasks

  • Related Content

Overview of Restore Scenarios

A restore scenario in SQL Server is the process of restoring data from one or more backups and then recovering the database. The supported restore scenarios depend on the recovery model of the database and the edition of SQL Server. 

The following table introduces the possible restore scenarios that are supported for different recovery models.

Restore scenario

Under simple recovery model

Under full/bulk-logged recovery models

Complete database restore

This is the basic restore strategy. A complete database restore might involve simply restoring and recovering a full database backup. Alternatively, a complete database restore might involve restoring a full database backup followed by restoring and recovering a differential backup.

For more information, see Complete Database Restores (Simple Recovery Model).

This is the basic restore strategy. A complete database restore involve restoring a full database backup and, optionally, a differential backup (if any), followed by restoring all subsequent log backups (in sequence). The complete database restore is finished by recovering the last log backup and also restoring it (RESTORE WITH RECOVERY).

For more information, see Complete Database Restores (Full Recovery Model)

File restore *

Restore one or more damaged read-only files, without restoring the entire database. File restore is available only if the database has at least one read-only filegroup.

Restores one or more files, without restoring the entire database. File restore can be performed while the database is offline or, for some editions of SQL Server 2005 and later versions, while the database remains online. During a file restore, the filegroups that contain the files that are being restored are always offline.

Page restore

Not applicable

Restores one or more damaged pages. Page restore can be performed while the database is offline or, for some editions of SQL Server 2005 and later versions, while the database remains online. During a page restore, the pages that are being restored are always offline.

An unbroken chain of log backups must be available, up to the current log file, and they must all be applied to bring the page up to date with the current log file.

For more information, see Restore Pages (SQL Server).

Piecemeal restore *

Restore and recover the database in stages at the filegroup level, starting with the primary and all read/write, secondary filegroups.

Restore and recover the database in stages at the filegroup level, starting with the primary filegroup.

* Online restore is supported only in SQL Server 2005 Enterprise Edition and later versions.

Regardless of how data is restored, before a database can be recovered, the SQL Server Database Engine guarantees that the whole database is logically consistent. For example, if you restore a file, you cannot recover it and bring it online until it has been rolled far enough forward to be consistent with the database.

[Top]

Advantages of a File or Page Restore

Restoring and recovering files or pages, instead of the whole database, provides the following advantages:

  • Restoring less data reduces the time required to copy and recover it.

  • On SQL Server 2005 Enterprise Edition and later versions, restoring files or pages might allow other data in the database to remain online during the restore operation.

[Top]

Recovery Models and Supported Restore Operations

The restore operations that are available for a database depend on its recovery model. The following table summarizes whether and to what extent each of the recovery models supports a given restore scenario.

Restore operation

Full recovery model

Bulk-logged recovery model

Simple recovery model

Data recovery

Complete recovery (if the log is available).

Some data-loss exposure.

Any data since last full or differential backup is lost.

Point-in-time restore

Any time covered by the log backups.

Disallowed if the log backup contains any bulk-logged changes.

Not supported.

File restore *

Full support.

Sometimes.**

Available only for read-only secondary files.

Page restore *

Full support.

Sometimes.**

None.

Piecemeal (filegroup-level) restore *

Full support.

Sometimes.**

Available only for read-only secondary files.

* Available only in the SQL Server 2005 Enterprise Edition and later versions.

** For the required conditions, see Restore Restrictions Under the Simple Recovery Model, later in this topic.

Important

Regardless of the recovery model of a database, a SQL Server backup cannot be restored by a version of SQL Server that is older than the version that created the backup. Thus, for example, a backup created on SQL Server 2012 cannot be restored by SQL Server 2008.

[Top]

Restore Scenarios Under the Simple Recovery Model

The simple recovery model imposes the following restrictions on restore operations:

If any of these restrictions are inappropriate for your recovery needs, we recommend that you consider using the full recovery model. For more information, see Backup Overview (SQL Server).

Important

Regardless of the recovery model of a database, a SQL Server backup cannot be restored by a version of SQL Server that is older than the version that created the backup. Thus, for example, a backup created on SQL Server 2012 cannot be restored by SQL Server 2008.

[Top]

Restore Under the Bulk-Logged Recovery Model

This section discusses restore considerations that are unique to bulk-logged recovery model, which is intended exclusively as a supplement to the full recovery model.

Note

For an introduction to the bulk-logged recovery model, see The Transaction Log (SQL Server).

Generally, the bulk-logged recovery model is similar to the full recovery model, and the information described for the full recovery model also applies to both. However, point-in-time recovery and online restore are affected by the bulk-logged recovery model.

[Top]

Restrictions for Point-in-time Recovery

If a log backup taken under the bulk-logged recovery model contains bulk-logged changes, point-in-time recovery is not allowed. Trying to perform point-in-time recovery on a log backup that contains bulk changes will cause the restore operation to fail.

Restrictions for Online Restore

An online restore sequence works only if the following conditions are met:

  • All required log backups must have been taken before the restore sequence starts.

  • Bulk changes must be backed before starting the online restore sequence.

  • If bulk changes exist in the database, all files must be either online or defunct. (This means that it is no longer part of the database.)

If these conditions are not met, the online restore sequence fails.

Note

We recommend switching to the full recovery model before starting an online restore. For more information, see Recovery Models (SQL Server).

For information about how to perform an online restore, see Online Restore (SQL Server).

[Top]

Database Recovery Advisor (SQL Server Management Studio)

The Database Recovery Advisor facilitates constructing restore plans that implement optimal correct restore sequences. Many known database restore issues and enhancements requested by customers have been addressed. Major enhancements introduced by the Database Recovery Advisor include the following:

  • **Restore-plan algorithm: ** The algorithm used to construct restore plans has improved significantly, particularly for complex restore scenarios. Many edge cases, including forking scenarios in point-in-time restores, are handled more efficiently than in previous versions of SQL Server.

  • **Point-in-time restores: ** The Database Recovery Advisor greatly simplifies restoring a database to a given point in time. A visual backup timeline significantly enhances support for point-in-time restores. This visual timeline allows you to identify a feasible point in time as the target recovery point for restoring a database. The timeline facilitates traversing a forked recovery path (a path that spans recovery forks). A given point-in-time restore plan automatically includes the backups that are relevant to the restoring to your target point in time (date and time). For more information, see Restore a SQL Server Database to a Point in Time (Full Recovery Model).

For more information, see about the Database Recovery Advisor, see the following SQL Server Manageability blogs:

Arrow icon used with Back to Top link[Top]

Related Content

None.

[Top]

See Also

Concepts

Backup Overview (SQL Server)