Job Settings

⌘K
  2. Home
  4. Docs
  6. Job Settings
  8. Backup Job
  10. Backup Job Options
  12. SQL Server

SQL Server

Microsoft SQL Server (local)

 

  • Place the backups for each database into its own subfolder. By default, backup files are placed in the directory specified as the destination. If this option is enabled, for each database backup or folder backup, a separate directory will be created with the name matching the database (or folder), and the corresponding backups will be stored in this directory.
  • Enable CheckSum. This option adds the WITH CHECKSUM command to the backup process. In this case:
    • During backup creation, data page checksums of the database files will be verified. If an error is detected, the backup process will be terminated.
    • Checksums will be added to the backup file, and these checksums will be validated during a restore operation. If errors are found, the restore process will be interrupted, but the backup can still be restored.

    Errors may arise due to device issues, such as the appearance of “bit rot.” Enabling this option negatively impacts performance but helps identify situations where data corruption occurs at the byte level.

  • Verify After Backup. When this option is enabled, a RESTORE VERIFYONLY operation will be performed after the backup. Essentially, this option verifies that the created backup is valid and can be restored successfully. For more details, please refer to the documentation. More details…
  • Copy Only. This option adds the WITH COPY-ONLY command to the backup. Full and transaction log backups created with this option do not affect other backup options and are not combined into backup chains. Typically, enabling this option is necessary if you require an additional backup while your primary backups are managed by another backup job or software.
  • Backup one DB, send, repeat (as opposed to backup all, then send all). By default, SQLBackupAndFTP first creates backups of all databases in a temporary folder and then moves the backups from the temporary folder to the selected destinations. If this option is enabled, each backup will be immediately sent to the destinations after it is created. This option is useful if you have limited space for temporary local backup storage.
  • Backup only Online databases. By default, if a database backup fails for any reason, the backup job terminates with an error. However, when this option is enabled, databases that are offline will not cause an error but instead will display a message indicating that the backup for these databases was skipped.
  • SQL Server backup compression. This option controls the enabling or disabling of native SQL Server compression during backup creation. It can take three values:
    • Default: The parameter is not specified. The value specified for the entire SQL Server is applied.
    • On: The WITH COMPRESSION command will be added to the backup. The backup will be compressed on the fly. This may slow down the backup creation process, but the overall backup job execution time will be faster because the compression specified in the backup job can be turned off. Note note that if this option is enabled, ensure that SQLBackupAndFTP compression is disabled.
    • Off: The WITH NO_COMPRESSION command will be added to the backup. The backup will not be compressed on the fly, even if the SQL Server settings specify global compression for all backups.
  • Backup broken chain behavior. This option governs the behavior of the application when it detects that a backup was performed outside of SQLBackupAndFTP without using the COPY_ONLY option, resulting in a situation where differential or transaction log backups cannot be restored via application interface due to a broken backup chain. The available options are:
    • Generate Warning: A warning will be logged indicating that this situation occurred. The backup cannot be restored from the application interface (but can be restored manually if the correct backup set is assembled).
    • Generate Error: The backup will be executed, but the job will terminate with an error, resulting in an error report being sent to the specified email address in the ‘Send Confirmation’ section.
    • Make Full Backup: Instead of creating a differential or transaction log backup, a full backup will be created.

Microsoft SQL Server (remote)

  • Place the backups for each database into its own subfolder. By default, backup files are placed in the directory specified as the destination. If this option is enabled, for each database backup or folder backup, a separate directory will be created with the name matching the database (or folder), and the corresponding backups will be stored in this directory.
  • Backup one DB, send, repeat (as opposed to backup all, then send all). By default, SQLBackupAndFTP first creates backups of all databases in a temporary folder and then moves the backups from the temporary folder to the selected destinations. If this option is enabled, each backup will be immediately sent to the destinations after it is created. This option is useful if you have limited space for temporary local backup storage.
  • Create database snapshot for backup (for export to be transactionally consistent). Generating a .sql backup takes time, during which the database may change. For example, the backup might include a record that references, via a foreign key, another record that did not make it into the backup. Such situations can lead to obvious issues during restoration as well as subtle problems related to table inconsistencies. To avoid this, you can enable this option. The application will create a database snapshot that captures the state of the database at a specific point in time. Once the backup is complete, the application will delete the snapshot.

Microsoft SQL Server Remote Backup Options

Click on the More settings link in “Backup options” to find the advanced settings.

  • Target engine type. This parameter allows you to specify the version of SQL Server or the type of engine (Azure SQL/SQL Server) for which the script will be generated. This option is useful if you need to restore the database to a server version different from the one used for the backup. Available options:
    • Current. The default value. The .sql file will be generated for the type and version of the database that was the source of the backup.
    • Standalone. The .sql file will be generated for a classic SQL Server installed on servers. In the additional field, you can specify the exact version of SQL Server.
    • SQL Azure. The  .sql file will be generated for an Azure SQL database hosted in the Azure cloud.
  • Continue Scripting on Error. During the script generation process, errors may occur for some non-typical database objects. You can enable this option if these objects are not needed in your backup. However, keep in mind that this option might cause important data to be omitted from the backup, so use it at your own risk.
  • Use “IF NOT EXISTS.” Instructions will be added to the .sqlfile to skip the creation of an object from the backup if such an object already exists in the database.