To create a Maintenance Job, click on ‘Job’ > ‘Add Maintenance Job’ or click on the plus icon and select ‘Add Maintenance Job’.

Connect to DBMS

Log on to the server where a maintenance job will be run. To do this, click on the gear button in the ‘Log on to the server’ section and configure your server connection.

Add Custom Script

SQLBackupAndFTP allows you to add SQL scripts or Windows batch (*.cmd, *.bat) scripts.

Windows batch (*.cmd, *.bat) scripts

To add a Windows batch (*.cmd, *.bat) script, go to the ‘Run custom scripts’ section and click on the ‘Add’ link next to ‘Windows batch (*.cmd, *.bat)’.

CMD scripts are regular Windows shell scripts, such as batch files .bat. They are executed in the same location where the application is installed, on behalf of the service (typically using the Local System user by default).

When creating a script, please consider the following:

• Any non-zero exit code will result in an error for this step in the job.
• Conversely, even if there were errors within the script, if the last command in the script executed successfully, the job will not report any errors.

If you want to execute a PowerShell script, use powershell.exe and pass the script as an argument.

powershell.exe -File script.ps1

The following variables will be available in the script:

• SQLBAK_BACKUP_TYPE (FULL, FULL_COPY, DIFF, TRAN_LOG, TRAN_LOG_COPY) – The backup type specified at the beginning of the backup job (set in the scheduler).
• SQLBAK_JOB_SUCCESS(1 – Success, 0 – Failure) – A flag indicating the presence of errors in the backup job, available only in the After backup section.

SQL scripts

To add an SQL script, go to the ‘Run custom scripts’ section and click on the ‘Add’ link next to ‘SQL’.

Two variables will be available in the script:

• @SQL_BACKUP_TYPE – This variable contains a string with the backup type code, for example DIFF for Differential SQL Server backups.
• @SQLBAK_JOB_SUCCESS – This variable contains a flag (1or 0) indicating whether the backup job was completed successfully or with errors. This variable is only available to scripts that are run at the end of the job.

By default, the script executes in the context of the default database. To change the context to a different database, you can use the standard SQL command USE [db_name]

For PostgreSQL, the connection is made to the default database postgres. However, since PostgreSQL does not support changing the database context within a single connection to the database, a special syntactic sugar is provided: --@psql /c postgres;. Instead of the postgres database, you can specify a custom database.

For example, with SQL Server, you can add the execution of the DBCC CHECKDB operation before creating a backup to avoid perpetuating errors in the database.

Set Schedule

To run scripts automatically, turn on the ‘Schedule maintenance’ option. Access advanced settings by clicking on the ‘gear’ icon.

Send Confirmation

Enable this option if you prefer to receive email notifications when a maintenance job runs successfully or fails. Click on the ‘gear’ icon to access advanced and custom settings.