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

Reviewing the available backup methods

The plug-in offers the following backup methods:

The plug-in supports implementing an Online VDI or a pure VSS backup strategy, not a combination of the two.

Online Virtual Device Interface (VDI)

Microsoft supports the ability to perform online backups that use the VDI Application Program Interface (API) to integrate with a range of backup devices supported by NetVault Backup. Online VDI backups provide maximum reliability and flexibility when defining a backup strategy. This method supports the full range of backup types and options available with SQL Server’s Transact SQL language along with the ability to handle several recovery scenarios.

The backup-and-restore strategies available with the Online through VDI Backup Method are thoroughly discussed in the Backing Up and Restoring Databases section of the SQL Server Books Online.

Plug‑in for SQL Server’s Online through VDI backup method is the preferred backup method.

In VDI backup method, Plug‑in for SQL Server creates N (where, N represents the number of databases) number of user connections with SQL Server. Maximum one user connection is active at a time.

NetVault Backup supports VDI backup for databases with names that do not exceed 117 characters in length.

To perform VDI backups, NetVault Backup Plug‑in for SQL Server, use the BACKUP DATABASE Transact-SQL command. The Plug‑in for SQL Server uses the BACKUP DATABASE command that includes the NAME clause with a limit of 128 characters. The Plug‑in for SQL Server constructs the value to be passed to the NAME clause, using the database name, and a timestamp. Given the length in characters of the timestamp, results in 117 characters in length available for the name of the database.

If you are performing VDI backups including databases with names exceeding 117 characters in length, the backups complete with warnings, or with failed status. The NetVault Backup binary log shows the following ODBC message:

If you are performing backups using the VDI backup method, Quest recommends the name of the databases in your environment, to not exceed 117 characters. This limitation does not apply to backups performed using the VSS backup method.

Volume Shadow Copy Service (VSS)

Microsoft supports the ability to create snapshots of SQL Server data using VSS. VSS allows volume backups to be performed while applications on the system continue to write to the volumes. Microsoft provides a SQL Server VSS Writer that permits backup programs such as Plug‑in for SQL Server to copy SQL Server data while SQL Server is running. VSS-based backups do not negatively affect SQL Server’s performance or stability.

In VSS backup method, Plug‑in for SQL Server creates 2*N (where, N represents the number of databases) number of user connections with SQL Server. Maximum N user connections are active at a time.

Using VSS, you can:

NOTE: The Backup Files to Storage option is supported with any disk-based storage. To use the Retain Snapshot as Persistent and Discard After options, the data that you back up must reside on a NetVault Backup-supported disk array. Also, for persistent snapshots, only the metadata is copied to the target.

Reviewing SQL Server recovery models

When a database is created, a recovery model is enabled. Microsoft defines a recovery model as a “database property that controls the basic behavior of backup and recovery of the database.” The database’s recovery model controls how its transactions are logged, whether the transaction log can be backed up, and which kinds of restores are supported. SQL Server provides three different recovery models: Simple, Full, and Bulk-Logged.

Simple Recovery Model: With a Simple Recovery Model, log backups are not supported. Therefore, changes since the most recent backup are not protected. In the unfortunate event of failure, these changes must be re-run. PIT recovery is not allowed.
Full Recovery Model: Full Recovery Model databases require log backups; therefore, no work is lost due to a lost or damaged data file. PIT recovery is supported, assuming backups are complete up to the point-of-failure.
Bulk-Logged Recovery Model: Bulk-Logged Recovery Model databases require log backups. The Bulk-Logged Recovery Model is a variation of the Full Recovery Model that permits high-performance bulk-copy operations. This model reduces log space usage by bulk-logging most bulk operations. If a log is damaged or bulk-operations have occurred since the most recent Transaction Log backup, these changes must be re-run. PIT recovery is not supported for bulk-logged databases.

Consider the following when choosing a recovery model for a database:

Simple Recovery Model: The Simple Recovery Model should only be enabled for databases that are not updated frequently such as test, development, or databases mostly containing read-only data.
Full Recovery Model: The Full Recovery Model should be enabled for transactional databases where full recoverability and preventing work loss in a full range of recovery scenarios is required.
Bulk-Logged Recovery Model: The Bulk-Logged Recovery Model should be used temporarily when bulk operations, such as bulk inserts or index creation, are performed on Full Recovery Model databases. The Bulk-Logged Recovery Model increases performance and reduces log space consumption during these operations; you can switch databases back to full recovery immediately after the bulk operations have completed.

For more information, see Recovery Models and Transaction Log Management in the SQL Server Books Online.

관련 문서