Chat now with support
Chat with Support

NetVault Plug-in for SQL Server 11.2 - User Guide

Introducing Quest® 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 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
Restoring data Troubleshooting

Full recovery model backup sequences

•
Full Database and Incremental Transaction Log backups: When requirements mandate PIT data protection, performing Full Database backup nightly plus Incremental Transaction Log backups every four to eight hours should be sufficient when one or more of the following conditions exist:
•
Backup windows are large.
•
Databases are small.
•
Updates are infrequent across the entire database.
For example, Full Database backups are performed every night while Incremental Transaction Log backups are performed every four to eight hours. Each Incremental Transaction Log backup includes the transaction logs since the last Full Database or Incremental Transaction Log backup.
Restore sequences that use Incremental Transaction Log backups require that every Incremental Transaction Log backup between the last Full Database backup and the point-of-failure is restored in succession. This process can lead to longer restores and increased intervention to initiate multiple restore jobs. To speed up restores, include Differential Database backups, which reduces the number of Incremental Transaction Log backups that must be restored.
•
Full Database and Differential Database and Incremental Transaction Log backups: When requirements mandate PIT data protection and restores of the complete database must be completed quickly, Full Database plus Differential Database plus Incremental Transaction Log backups is an ideal strategy.
For example, Full Database backups are performed every Sunday night at 11:00 P.M. Differential Database backups are performed Monday through Saturday at 11:00 P.M. Incremental Transaction Log backups are performed every hour. Each Differential Database backup includes all the changes since the last Full Database backup, and each Incremental Transaction Log backup includes the transaction logs since the last Differential Database backup.
Restore sequences that use Incremental Transaction Log backups require that every Incremental Transaction Log backup between the last Full Database or Differential Database backup and the point-of-failure is restored in succession. This process can lead to longer restores and increased intervention to initiate multiple restore jobs. To speed up restores, increase the frequency of the Differential Database backups to every four to eight hours. This increase reduces the number of Incremental Transaction Log backups that must be restored. This strategy provides complete protection of the entire database with the simplest administration while supporting multiple restore scenarios.
•
Full File and Filegroup and Differential File and Filegroup and Incremental Transaction Log backups: When requirements mandate PIT data protection and restores must be completed quickly, Full File and Filegroup plus Differential File and Filegroup plus Incremental Transaction Log backups is an ideal strategy. This strategy lets you restore a complete database or the files or filegroups that are damaged. By being able to restore only what is needed, restores are faster and data loss is minimized.
For example, Full File and Filegroup backups are performed every Sunday night at 11:00 P.M. Differential File and Filegroup backups are performed Monday through Saturday at 11:00 P.M. Incremental Transaction Log backups are performed every hour. Each Differential File and Filegroup backup includes all changes since the last Full File and Filegroup backup, and each Incremental Transaction Log backup includes the transaction logs since the last Differential File and Filegroup backup.
Restore sequences that use Incremental Transaction Log backups require that you restore every Incremental Transaction Log backup between the last Full File and Filegroup or Differential File and Filegroup backup and the point-of-failure in succession. This process can lead to longer restores and increased intervention to initiate multiple restore jobs. To speed up restores, increase the frequency of the Differential File and Filegroup backups to every four to eight hours. This change reduces the number of Incremental Transaction Log backups that must be restored. This strategy provides complete protection of the entire database with the flexibility to restore only the files or filegroups that are damaged while supporting most restore scenarios.

Defining a VSS backup strategy and reviewing types

Plug‑in for SQL Server provides the following types of VSS backups:

•
Full Database backup: Full Database backups are supported by:
•
SQL Server® versions: 2005 and later
•
Recovery models: All
A VSS Full Database backup backs up database data and all the log files necessary to bring the database up to a transactionally consistent state at restore time. Full Database backups consume more space and time per backup and are typically supplemented by differential backups, which are created more frequently. With Full Database backups, you can re-create an entire database in one step by restoring the database.
•
Differential Database backup: Differential Database backups are supported by:
•
SQL Server® versions: 2005 and later
•
Recovery model: All
A VSS Differential Database backup backs up only the data that has changed since the most recent base Full Database backup. A Differential Database backup contains only those parts of the database files that have changed. Differential Database backups are smaller and faster to create than the Full Database backups. A Differential Database backup is useful if some of the database’s tables are modified more frequently than others. In this case, Differential Database backups allow you to back up frequently without the overhead of Full Database backups.
 
* 
IMPORTANT: If a failover occurs in an Active/Passive Failover Cluster and your VSS-based backup strategy includes Differential Database backups, perform a Full Database backup of the modified configuration as soon as possible. When the plug-in detects that a failover occurred and a different node holds the active role, the plug-in automatically runs a full backup for the next scheduled backup. However, if you must perform a restore to the new active node before a new full backup is run, database changes that occurred between the last full backup and the new full backup might be lost.
•
Copy-Only backup: Copy-Only backups are supported by:
•
SQL Server® versions: 2005 and later
•
Recovery models: All
VSS Copy-Only backups are independent of the normal sequence of backups. Unlike a full backup, a Copy-Only backup cannot serve as the base backup for a series of differential backups. Also, performing a Copy-Only backup does not affect what is backed up by the next differential backup. Copy-Only backups are ideal for situations that require special purposes, such as creating test databases.

Examples of VSS backup sequences

Following are few examples of backup sequences that support multiple restore scenarios and that you can implement to meet your SQL Server® data protection requirements.

•
Full Database backups only: When requirements guarantee data protection up to the previous day, performing Full Database backups nightly should be sufficient when one or more of the following conditions exist:
•
Backup windows are large.
•
Databases are small.
•
Updates are infrequent across entire database.
•
Databases are only for testing or development purposes.
•
Entire database is read-only.
•
Full Database and Differential Database backups: When requirements guarantee data protection up to the previous day and backups must be completed quickly, Full Database backups coupled with Differential Database backups is a valid strategy. For example, Full Database backups are performed every Sunday night at 11:00 P.M., while Differential Database backups are performed Monday through Saturday at 11:00 P.M. Each Differential Database backup includes all the changes since the last Full Database Backup, which is known as the differential base.
No matter when recovery is performed, the same number of restore jobs is required. For example, if recovery is performed on Tuesday, Sunday’s Full Database backup and Monday’s Differential Database backup must be restored. Whereas, if recovery is performed on Thursday, Sunday’s Full Database backup followed by Wednesday’s Differential Database backup must be restored.
Even though Differential Database backups increase not only in size but in duration, restores are quicker due to the fewer number of restore jobs that must be run.
•
Copy-Only backups: When requirements demand that a backup be created without interfering with regular scheduled backup sequences, Copy-Only backups is a valid strategy. Often times, you must create a full backup outside of the regular backup schedule, such as when refreshing your test databases. This process could potentially interfere with the regular backup schedule and throw off subsequent differential backups. The Copy-Only backup allows administrators to run non-regular Full Database backups at any time without interfering with scheduled backups.

Backing up system databases

The SQL Server® system databases are essential for the operation of a server instance. These backups enable recovery of the SQL Server if a system failure occurs, such as loss of a hard disk.

The system databases that you must always back up include msdb, master, and model. The master and msdb databases have a Simple Recovery Model while the model database has a Full Recovery Model. For replicated databases, you should also back up the distribution database.

•
master: The master database records system-level information for a SQL Server system. For the master database, SQL Server supports only Full Database backups. Quest recommends that you schedule routine Full Database backups of master.
•
msdb and model: SQL Server uses the msdb database to store data. SQL Server uses the model database as a template when you create a user database. Back up the model and msdb databases whenever they are updated. You can back up the model and msdb databases in the same way that you back up user databases. Quest recommends that you create only Full Database backups of the model database. Because it is small and rarely changes, backing up the log is not required.
 
* 
NOTE: The plug-in cannot back up tempdb or Resourcedb databases.

For more information, see Backing Up and Restoring System Databases in SQL Server Books Online.

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating