Log backups

Archived log files for databases contain committed transaction data. This transaction data can be used to run a rollforward recovery process as part of a restore operation. Using archive log backups enhances the recovery point objective for your data. Ensure that log backups are enabled in your backup jobs to allow rollforward recovery when you restore Microsoft SQL Server data.

Restriction: It is possible that the same databases that are on a VM might be backed up as part of a VM backup job and a SQL Server backup job. If enable log backup for a SQL Server backup job, ensure that Truncate SQL logs option is not selected for a VM backup job that backs up the same databases. The log truncation deletes all inactive logs from the log file. The deleted log sequence causes discontinuity in the log backup.
You can perform log backups by using one of the following methods.
Common log backup approach
This method applies to all resources that are associated with an SLA policy. Select Enable Log Backup option to enable the backing up of transaction logs. When you enable log backups for the first time, you must run a backup job for the SLA policy to activate log archiving to IBM Spectrum Protect Plus on the database. This backup creates a separate volume on the vSnap repository and the volume is mounted persistently on the SQL application server. The volume remains mounted on the SQL application server unless the Enable Log Backup option is cleared and a new backup job is run. To enable log backups, see topic enable log backup under step 5 in Backing up SQL Server data.
Advanced SQL log backup approach
This method is only applicable to resources associated with an SLA policy that has log backup sub policy enabled. This log backup option is an add-on to the existing Enable Log Backup option assigned to the database (Inventory screen). To enable the log backup option on an existing SLA policy, the user can edit the existing SLA policy and add the log backup sub policy. When the log backup sub policy is enabled for multiple resources in the same database, only the log backup operation on the first SLA policy is performed. This backup does not create a separate volume on the vSnap repository. For more information about log backups SLA policy for SQL, see step 5.d in Creating an SLA policy for databases and file systems.
Important: If a database is protected with the log backup sub policy, scheduled log backups for that database will be automatically disabled.
Review the following criteria before you set up log backup operations:
  • To run log backups, the SQL Server agent user must be a local Windows administrator. This user must have sysadmin permission to manage SQL Server agent jobs. The agent uses that administrator account to enable and access log backup jobs. For each SQL Server instance, the SQL Server agent user also must be the user of the SQL Server service and the SQL Server agent service account. This rule is true for every SQL Server instance to be protected.
  • IBM Spectrum® Protect Plus does not support log backup operations for Simple recovery models.
  • Avoid configuring log backups for a single SQL database by using multiple backup jobs. Logs are truncated during log backup operations. If a single SQL database is added to multiple job definitions with log backup enabled, a log backup from one job will truncate a log before the next job backs it up. This overlap might cause point-in-time restore jobs to fail.
  • IBM Spectrum Protect Plus supports database backups and transaction log backups. The product name is populated in the msdb.dbo.backupset for records that are created by backups that are initiated from IBM Spectrum Protect Plus.
  • IBM Spectrum Protect Plus automatically truncates post log backups of databases that it backs up. If database logs are not backed up with IBM Spectrum Protect Plus, logs are not truncated and must be managed separately.
  • When an SQL backup job is completed with log backups enabled, all transaction logs up to the completion of that job are purged from the SQL Server. Log purging occurs only if the SQL backup job is completed successfully. If log backups are not backed up during a rerun of the job, log purging does not occur.
  • A log backup operation for a secondary SQL Server Always On database can fail with the following error: 
    Log backup for database 'DatabaseName' on a secondary replica failed because a synchronization point could not be established on the primary database.
    If this error occurs, change the backup preference of the availability group to Primary. Logs are then backed up from the primary replica. After a successful log backup of the primary replica is successfully completed, the backup preference can be changed.
  • If a source database is overwritten, all previous transaction logs up to that point are placed in a condense directory after the original database is restored. When the next run of the SQL Server backup job is completed, the contents of the condense folder are removed.