Restore and Recovery Overview (SQL Server)

In this 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.

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.

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

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

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 the Enterprise edition.

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.

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 restoring files or pages might allow other data in the database to remain online during the restore operation.

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.

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.

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 ordefunct. (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).

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: