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.

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, follow the instructions in Backing up SQL Server data.

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.
  • Before the logs are copied to the vSnap repository, IBM Spectrum Protect Plus uses the backup folder that is configured for the SQL Server instance as the staging area to collect logs. The volume where this folder is located must have sufficient space to contain the transaction logs between backup jobs. The staging area can be modified by changing the backup folder configuration in SQL Server Management Studio (SSMS).
  • 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.