Restore SQL Server Data

You can browse and restore backed up data for your instance, partial database or an entire database.

Restore Objectives

Your objective for restoring data determines the process you must follow and the restore destination.

Restore Objective

Description

Disaster recovery

Plan your full system recovery in advance.

Recover data from long-term storage

If you back up your data to tape for long-term storage, you must identify the corresponding media, and make sure that it is available for the recovery operation.

Restore data from a specified time range

If you need to restore data during a range of time (for example, data that was deleted accidentally), you can browse and restore data based on a time range that you specify. Typically, this data would be restored to an out-of-place destination.

Restore to a point in time

If your SQL database becomes corrupted or otherwise invalid, you can restore the database to a point in time before the database became unusable. Typically, this data would be restored to an in-place destination.

Restore Methods

You can restore backed-up data by using the following methods:

  • Command Center

    You can perform the browse and restore operation from the Command Center. For more information, see SQL Restores.

  • CommCell Console

    You can perform the browse and restore operation from the CommCell Console.

    You can use Commvault Plug-in to perform Commvault backup and restore operations in the SQL Management Studio. For more information, see Commvault Plug-in for SQL Management Studio.

  • Command Line

    The command line interface provides a way to restore data from the command line, where various restore options can be specified either as parameters on the command line or in a separate arguments file. For more information, see Restoring SQL Server Agent Using the Command Line Interface.

Factors in Choosing the Appropriate Restore Methods and Options

When you select a restore time, the software determines the optimal combination of full backup, differential backup, and log backups necessary to bring the restored databases back to the selected time. This combination of backups is called the restore chain.

  • For basic database restores, the restore chain consists of the most recent full backup, the latest differential backup (if any), and all the subsequent log backups (if any) taken prior to the selected restore time.

  • While restoring databases to a transaction mark or during a point-in-time restore, the restore chain consists of the most recent full backup, the latest differential backup (if any), all the subsequent log backups taken prior to the selected browse time, and the next log backup after the browse time. The transparent inclusion enables restores to a selected point in time.

Scope

To what degree does data need to be restored?

  • To restore all or part of a client's data, you can perform a Browse and Restore operation from the CommCell Console. If the system is not available, but the media is intact, then use Media Explorer to perform the restore.

  • To restore the full system (operating system and application) on a client, perform a Full System Recovery: SQL Server Agent.

Time

Do you need to restore data from the latest available backup data or from a previous point in time?

Note

Browse from subclient level is not supported for the SQL Agent.

Block-Level Restores and SQL Recover Point Restores

Unless otherwise noted for a particular feature, block-level browse is not supported from backups to tape libraries or virtual tape libraries. If you want to perform either of these restore operations by using a secondary copy on tape libraries or virtual tape libraries, move the copy to disk storage before performing the restore.

Restore Chains

When you select a restore time, the software determines the optimal combination of full backup, differential backup, and log backups necessary to bring the restored databases back to the selected time. This combination of backups is called the restore chain.

  • For basic database restores, the restore chain consists of the most recent full backup, the latest differential backup (if any), and all the subsequent log backups (if any) taken prior to the selected restore time.

  • While restoring databases to a transaction mark or during a point-in-time restore, the restore chain consists of the most recent full backup, the latest differential backup (if any), all the subsequent log backups taken prior to the selected browse time, and the next log backup after the transparent browse time. The transparent inclusion enables restores to a selected point in time.

Restore Destinations

The destination where you restore data is determined by your restore objective. Generally, you perform an in-place or an out-of-place restore.

Restores to a SMB or CIFS share, mapped drives, and network paths are currently not supported.

In-place restore

When you restore a database in place, you restore it to the same path on the same client from which the database was backed up.

Out-of-place restore

When you restore a database out of place, you can restore it to any one of the following destinations:

  • A different client from which the database was backed up

  • A different path on the same client from which the database was backed up

  • A different database

When you restore a database out of place, the destination instance must be the same version or a more recent version of SQL than the source instance.

SQL Portability

You can use the SQL Server Agent to perform the following cross-server restore operations.

Source

Destination

Windows

Linux

Azure

AWS

Windows

Yes

Yes

Yes, but it must be on the same proxy.

Yes

Linux

Yes

Yes

No

No

Azure

Yes

No

Yes

No

AWS

Yes

No

No

Yes

For a video about SQL database restores, see How to perform a SQL database restore.

Loading...