This document is not supported any longer.Decide on Backup and Recovery Tool
You should also find out what tools are used to backup SQL Server VM hosting your database(s) and their transaction logs in your organization infrastructure.
Using Veeam
If you plan to use Veeam Backup & Replication for both image-level backup of SQL Server VM and database transaction log backup, then you will need to select the corresponding option (Process transaction logs with this job) when configuring VM processing settings in backup job. Job configuration will be discussed later in more details.
This option allows you to ‘set and forget’ an automated workflow for creating all the backups required for SQL Server database recovery using Veeam Backup & Replication. Thus, data can be quickly and easily recovered, following the scenario of your choice with Veeam tools.
With this option selected, you can configure SQL database log backup settings on the SQL tab, which becomes available to you.
|
For other applications, this option triggers transaction log truncation. |
Using 3rd Party Tool
For large SQL Server deployments, frequent full database backups are not always acceptable since these operations would be very much time- and resource-taking. For this reason, organizations prefer to create full and differential database backups periodically, backing up transaction logs quite frequently. Therefore, to provide for proper restore, database administrators need to ensure they have the most recent full/differential backup and the associated sequence of transaction log backups at hand. When Veeam creates an image-level backup of SQL Server VM, using application-aware processing without special settings (that is, without COPY_ONLY flag), this is always treated by SQL as a full database backup.
|
Per Microsoft recommendation, starting with Veeam Backup & Replication 9.0, a Veeam backup job registers a SQL Server database backup by adding the corresponding record in the system.db system table of each SQL Server database. This feature is known as database labeling. |
As a result, this full backup becomes a new base for the differential backups that follow, jeopardizing the sequence (see this illustrated article).
Thus, if you plan to preserve the chain of full and differential database backups (created by native means or by 3rd party backup tool) but you also want Veeam to back up your SQL Server VM, it is recommended that you trigger the COPY_ONLY flag for Veeam. For that, select the Perform copy only option.
This option indicates that a chain of database backups and transaction logs is created by native means or by 3rd party tool, and instructs Veeam to keep this sequence untouched. For that, Veeam will back up the specified SQL Server VM using the VSS_BS_COPY method for snapshot creation. Transaction logs will not be affected in any way, so your database administrator can handle them with native or 3rd party tools.
|
With the Perform copy only option selected, the SQL and Oracle tabs intended for log handling settings will be hidden, with their parameters unavailable to user. |
The COPY_ONLY method also allows Veeam to support Always-On Availability Groups, creating backups of passive replicas.
For detailed description of SQL Server backup chain, you can read the article by Michael K. Campbell at http://sqlmag.com/blog/breaking-backup-chain-redux-or-eating-crow and the recommendations by Paul Randal at http://www.sqlskills.com/blogs/paul/backup-with-copy_only-how-to-avoid-breaking-the-backup-chain/.
Also, refer to the Veeam Backup & Replication User Guide and to the white paper by Tibor Karaszi for information on SQL Server backup: https://www.veeam.com/wp-sql-server-backup-with-veeam.html.