Chat now with support
지원 담당자와 채팅

NetVault Backup Plug-in for SQL Server 12.2 - User Guide

Introducing NetVault Backup Plug-in for SQL Server Planning your SQL Server deployment Installing and removing the plug-in Configuring the plug-in Backing up data
Defining a backup strategy Understanding snapshot-based backups Reviewing the compression features Performing Online VDI backups Performing VSS backups in SQL Server Example of creating a full VDI backup of an AlwaysOn Availability Group on Windows
Restoring data Using the CLI with the plug-in Troubleshooting

Restoring data: an overview

Restoring is the process of copying data from a backup and applying transaction logs to the data to roll it forward to the target recovery point. A backup contains sufficient transaction log records to allow rolling forward the active transactions as part of restoring each backup. Each backup also contains sufficient transaction logs to roll back any uncommitted transactions to bring the database to a consistent, usable state. The process of rolling forward uncommitted transactions, if any, and bringing the database online is known as Recovery.

This topic includes the following subtopics:

Reviewing the phases of the VDI restore sequence

Each SQL Server restore scenario is implemented using one or more restore operations, which is called a restore sequence. A restore sequence moves data through one or more phases of restore. The possible phases of a restore include the data copy, redo (roll forward), and undo (roll back) phases, which are detailed in the following topics.

The first phase in any restore process is the data copy phase. The data copy phase involves copying all the data, log, and index pages from the backup media to the database files. It involves copying data from one or more of the following backup types:

Partial Database (Windows only)

Redo is the process of reapplying logged changes to the data in the roll-forward set to bring the data forward in time. To accomplish the redo, the SQL Server Database Engine processes Transaction Log backups as they are restored. The engine starts with the log contained in the data copy backups listed in the preceding topic and any Incremental Transaction Log backups that are then restored. Roll forward stops at the recovery point; this point is either the end of the log or a point specified in the SQL Server restore options for Incremental Transaction Log backups. Plug‑in for SQL Server supports both time-based and marked transaction-based PIT recovery.

After the redo phase has rolled forward all the log transactions, a database typically contains changes made by transactions that are uncommitted at the recovery point. This result makes the rolled forward data transactionally inconsistent. The recovery process opens the transaction log and identifies uncommitted transactions and rolls them back to bring the database into a transactionally consistent state. This step is called the undo phase.

If the data is transactionally consistent at the start of the recovery process, the undo phase is skipped.

After the database is transactionally consistent, recovery brings the database online.

Understanding the available types of Online VDI restores

To perform a successful restore, you must have a full understanding of the types of restores that are available for use. This topic outlines the possible restore scenarios supported for different recovery models.

The following restore scenarios are supported under Simple Recovery Model:

Complete database restore: With a Simple Recovery Model database, the goal of a complete database restore is to restore the entire database. This type of restore typically involves restoring a Full Database backup or a Full Database backup followed by a Differential Database backup. The entire database is offline during a complete database restore.
File restore (Windows only): In a file restore for a Simple Recovery Model database, the goal is to restore one or more damaged read-only files without restoring the entire database. This type of restore involves a Full File and Filegroup backup.
Partial database restore (Windows only): A partial restore for a Simple Recovery Model database includes restoring the primary filegroup and all read/write filegroups that were included in a Partial Database or Differential Partial Database backup. Restores of the read-only filegroups with a Full File and Filegroup backup are required only if the read-only filegroups are damaged.

The following restore scenarios are supported under the Full and Bulk-Logged Recovery Models:

Complete database restore: Under the Full and Bulk-Logged Recovery Models, the goal is to restore the entire database to the point-of-failure. A complete restore sequence typically includes:
1
Perform Tail-Log backup.
2
Restore Full Database or Full File and Filegroup backup while selecting With NO RECOVERY restore option.
3
Restore Differential Database or Differential File and Filegroup backup while selecting With NO RECOVERY restore option.
4
Restore all subsequent Incremental Transaction Log backups in order while selecting the With NO RECOVERY restore option.
5
Restore and recover the Tail-Log backup by selecting the With RECOVERY restore option.
File restore (Windows only): In a file restore for a Full or Bulk-Logged Recovery Model database, the goal is to restore the entire database while leaving the option available to restore damaged files without restoring the entire database. This type of restore involves the following restore sequence:
1
Perform Tail-Log backup.
2
Restore Full File and Filegroup backup using the With NO RECOVERY restore option.
3
Restore Differential File and Filegroup backup using the With NO RECOVERY restore option.
4
Restore all subsequent Incremental Transaction Log backups in order while selecting the With NO RECOVERY restore option.
5
Restore and recover the Tail-Log backup while selecting the With RECOVERY restore option.
Point-in-time (PIT) recovery: You can perform a PIT recovery on the transaction logs that are restored during an Incremental Transaction Log restore. Plug‑in for SQL Server supports both time-based — using hours:minutes:seconds — and marked-transaction-based PIT recovery. PIT recovery options are available when restoring Incremental Transaction Log and Tail-Log backups.
Time-Based Point-in-Time Recovery: Time-based PIT recovery is useful when the time that the data corruption occurred is known. For example, if a developer dropped a table at 6:00:00 A.M., PIT recovery can be performed with a stop time of 5:55:00 A.M. Time-based PIT recovery can only be used for unrelated databases; therefore, when multiple SQL Server databases are required to be logically consistent, marked-transaction PIT recovery should be used.
Marked-Transaction-Based Point-in-Time Recovery: SQL Server 2008 and later support the ability to insert named marks into a transaction log to allow recovery to a specific mark. This functionality is beneficial if you have two or more databases that must be logically consistent. You can implement special procedures to ensure the recoverability of these databases after a restore. Because you can only recover related databases to a marked transaction, your application must be configured to insert named marks into the transaction log before performing backups. For more information, see Ensuring Recoverability of Related Databases and the Recovering to a Marked Transaction in the SQL Server Books Online.

Restoring system databases for Online VDI restore with Plug-in for SQL Server

You can only restore system databases from backups that are created on the version of SQL Server that the instance is running. For example, to restore a system database on an instance that is running on SQL Server 2008, use a backup that was created after the instance was upgraded to SQL Server 2008.

The master database must be accessible and at least partly usable for starting a SQL Server Instance. If the master database becomes unusable, you can return the database to a usable state by either restoring the master from a current database backup or rebuilding the master.

You can restore the master database from a Full Database backup only if you are able to start the server instance. If severe damage to the master database prevents you from starting the SQL Server, rebuilding the master is the only option left. For more information, see Rebuilding the master Database in the SQL Server Books Online.

The restoration of the master database includes:

Restore a Full Database backup of the master database.
Because the changes made to master since the last backup were lost, re‑create them by performing those steps again.
After the master has been restored and changes reapplied, back up the master immediately.

For more information, see Restoring data to an alternate SQL Server.

Restoring the model or msdb database from a backup is required under the following circumstances:

The master database has been rebuilt. In this case, restore model from a backup because rebuilding master re-creates model.
The model or msdb database has been damaged, for example, due to media failure.

Restoring model or msdb is the same as performing a Complete Database restore of a user database.

Before restoring msdb, the SQL Server Agent Service in the Control Panel must be stopped. Quest also recommends that you shut down applications, because the restore procedure disconnects users when necessary.

관련 문서