SQL Backup Master Help

Online Documentation

Database options

SQL Backup Master offers  a range of options affecting how it interacts with SQL Server during backup operations.

  • Database timeout - The number of seconds SQL Backup Master will wait when establishing connectivity to SQL Server. This value is also used for normal database command operations, although some long-running commands (such as restore operations) will override it.
  • Add CHECKSUM option to database backup statements - If page checksums are turned on, you can use this option to validate them during backup.
  • Run RESTORE VERIFYONLY after backups - Runs a verification-only restore process after the backup completes (more information here).
  • Copy-only backups - Creates a backup that does not interfere with the existing SQL Server backup chain. A copy-only backup cannot serve as a differential base or differential backup and does not affect the differential base (more information here).
Because copy-only backups do not establish a differential base, enabling the copy-only option on a job whose schedule also includes differential and/or transaction log backups creates a backup chain configuration conflict. SQL Backup Master will ask for confirmation when saving such a configuration, and backup chain validation (see below) will report the conflict at backup time.

Backup Chain Validation

If multiple software applications are triggering the creation of full backups for the same SQL Server database(s), problems may occur on creation of subsequent differential and/or transaction log backups. This can result in backup log error messages like the following:

Failed to create backup: Cannot perform a differential backup for database “XYZ”, because a current database backup does not exist.

Enabling backup chain validation will cause SQL Backup Master to validate that it is the exclusive creator of all backups in the SQL Server backup chain. Before creating a differential backup, it queries the SQL Server backup history to confirm that it created the most recent full backup of the database. Before creating a transaction log backup, it confirms that it created the most recent backup of any type. A failed check is reported as a job warning or a job error, depending on which validation mode is selected.

When validation fails, the resulting log message includes details of the most recent backup that was not created by SQL Backup Master - including its creation time, creating user, and location. A location shown as a GUID (rather than a file path) indicates a snapshot backup created through the SQL Server VSS writer - typically by an image-level, virtual machine, or cloud backup product rather than by a person. Such products usually run on a recurring schedule, so the conflict will not be resolved by creating a new full backup with SQL Backup Master - the other product must be reconfigured (most offer a "copy-only" or "VSS copy backup" mode that leaves the SQL Server backup chain untouched).

Copy-only configuration conflicts - If the backup job itself is configured to create copy-only backups while its schedule includes differential and/or transaction log backups, chain validation reports an invalid backup chain configuration. Copy-only full backups cannot serve as the base for the job's own differential backups, and copy-only transaction log backups do not truncate the transaction log. Resolve the conflict by disabling either the copy-only option or the affected backup types.

For more information on troubleshooting backup chain invalidation, please see:

https://www.sqlbackupmaster.com/support/backupchainvalidation

Advanced Options

SQL Backup Master provides advanced users the option of passing arguments (parameters) to the backup command that it constructs at run-time.

You should use this feature ONLY if you fully understand the implications of any arguments you specify and are prepared to test them thoroughly.

SQL Backup Master recognizes a specific subset of backup command line arguments (unrecognized arguments are ignored):

  • COPY_ONLY
  • COMPRESSION
  • NO_COMPRESSION
  • SKIP
  • NOSKIP
  • FORMAT
  • NOFORMAT
  • BLOCKSIZE
  • BUFFERCOUNT
  • MAXTRANSFERSIZE
  • CHECKSUM
  • NO_CHECKSUM
  • REWIND
  • NOREWIND
  • UNLOAD
  • NOUNLOAD
  • ENCRYPTION

For details on the purpose of each of these arguments, see the documentation for your specific SQL Server version. Some arguments are available only in specific versions of SQL Server.

Multiple arguments should be separated by commas. For example:

COMPRESSION, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 4194304

Custom backup command arguments are applied to all backup types (full, differential, and log), with one exception: COPY_ONLY is not applied to differential backups, as SQL Server does not support that combination.