How to deploy SqlBak app automatically

When SqlBak is installed on a large number of servers, the deployment can be automated in 3 steps:

  1. Download and install the SqlBak app
  2. Connect the SqlBak app to sqlbak.com and to a DBMS
  3. Add a backup job from a .json file

.bat script template

Here’s an example of a .bat script to automate a deployment of a job for a local SQL Server. To connect to another DBMS or to create your own script – see the details for every step below.

@echo off
rem Step 0. Server - specific configuration

rem SQLBAK_KEY is a secret key to connect to your account found at https://sqlbak.com/download after you log in
set SQLBAK_KEY=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

set SETUP_FILE_PATH=.\SqlBakSetup.exe 
set APPLICATION_PATH=%ProgramFiles(x86)%\Pranas.NET\SqlBak

set COMPUTER_NAME=%COMPUTERNAME%
rem SQL Server credentials
set SQL_SERVER_USER=sa
set SQL_SERVER_PASSWORD=my-secret-pw

rem .json file can be downloaded by clicking the "Export to JSON" icon on a backup job's summary page. 
set JOB_JSON_CONFIGURATION=C:\Users\Me\Downloads\job_23364.json

rem Step 1. Download and install the SqlBak app

rem download the installer
curl.exe "https://sqlbak.com/download/windows/alpha" --output "%SETUP_FILE_PATH%"
if %ERRORLEVEL% neq 0 exit /b %ERRORLEVEL%
echo "SqlBak successfully downloaded"

rem install the SqlBak app
"%SETUP_FILE_PATH%" /S /D=%APPLICATION_PATH%
if %ERRORLEVEL% neq 0 exit /b %ERRORLEVEL%
echo "SqlBak successfully installed"

rem Step 2. Connect the SqlBak app to sqlbak.com and to a DBMS

rem connect the SqlBak app to your account on sqlbak.com
"%APPLICATION_PATH%\SqlBak.Job.Cli.exe" -install --secret-key=%SQLBAK_KEY% --server-name=%COMPUTER_NAME% --allow-add-index-to-name=Y --force=Y
if %ERRORLEVEL% neq 0 exit /b %ERRORLEVEL%

rem Connect to DBMS 
"%APPLICATION_PATH%\SqlBak.Job.Cli.exe" --save-connection --db-type=mssql --name=. --user=%SQL_SERVER_USER% --password=%SQL_SERVER_PASSWORD% --integrated-security=N
if %ERRORLEVEL% neq 0 exit /b %ERRORLEVEL%

rem Step 3. Add a backup job from a .json file

"%APPLICATION_PATH%\SqlBak.Job.Cli.exe" --import-job --secret-key=%SQLBAK_KEY% --file=%JOB_JSON_CONFIGURATION%
if %ERRORLEVEL% neq 0 exit /b %ERRORLEVEL%

Step 1. Download and install the SqlBak app

While you can use a previously downloaded SqlBakSetup.exe to install the app, it is recommended to always download the latest setup file from sqlbak.com.

The latest version of the application can be downloaded automatically using the curl utility. (Starting April 2018 Windows already ships with a copy of the curl. If you use the previous versions of Windows, download curl and install it)

curl.exe "https://sqlbak.com/Download/windows/alpha" --output ".\SqlBakSetup.exe"

The /S option allows you to run the installation in quiet mode

SqlBakSetup.exe /S /D="C:\Program Files (x86)\Pranas.NET\SqlBak"

Note: the application must be installed as an administrator.

SqlBakSetup installs SqlBak.Job.Cli.exe utility as part of SqlBak app. The utility will be used in the next steps. By default that tool is located in the following directory: C:\Program Files (x86)\Pranas.NET\SqlBak\SqlBak.Job.Cli.exe 

Step 2. Connect the SqlBak app to sqlbak.com and to a DBMS

Connect the SqlBak app to your account on sqlbak.com

SqlBak app communicates with your account on sqlbak.com. To connect it to your account, use the --install parameter

SqlBak.Job.Cli.exe -install -secretKey=xxxxxxxx-xxxx-xxxx-xxx-xxxxxxxxxxxx -serverName=my-server

The -install command has two crucial parameters:

  • -secretKey – can be found on the Download page after you login to sqlbak.com
  • -serverName – the name under which the server will be displayed on the Dashboard page. If omitted, the computer name will be used.

Connect the SqlBak app to your DBMS

To connect to a DBMS use the --save-connection command

SqlBak.Job.Cli.exe --save-connection --db-type=[db-type] --name=[adress] --user=[sa] --password=[secret-pwd]

The --save-connection command has the following parameters:

  • --db-type – connection type, supported values: sql-server-local, sql-server-azure, sql-server-amazon, sql-server-remote, mysql, mysql-phpmyadmin, postgresql
  • --name – address to connect to a DBMS, it can be localhost, IP-address, or just a dot (.) (for SQL Server)
  • --user – user name which is used to connect to a DBMS
  • --password – user password

For each type of a DBMS, there are additional parameters, see SqlBak.Job.Cli.exe Reference for more details.

Here is an example of how to set a connection to a local SQL Server:

SqlBak.Job.Cli.exe --save-connection --db-type=mssql --name=. --user=sa --password=my-private-password --integrated-security=N

Step 3. Add a job from a .json file

Start by creating a job via interface on sqlbak.com, export the job to a .json file, then use this file to add the same job to a new server.

Any job can be exported to a .json file and then imported via SqlBak.Job.Cli.exe tool.

To export your backup job’s settings in a .json file please click on the “Export to JSON” icon on your backup job’s summary page.

A file that contains a full description of the backup job (database list, destination list, schedule settings, compression type, folder backup settings, etc.) will be downloaded in .json format.

Here is a short example of a .json configuration file:

{
  "job_type": "backup",
  "name": "test server",
  "dbms_connection": {
    "dbms_type": "sql_server_local",
    "name": "."
  },
  "databases_backup_settings": {
    "databases": [
      "AdventureWorks"
    ],
    "excluded_databases": [],
    "is_all_non_system_databases": false
  },
......................
......................
  "job_options": {
    "backup_file_name_format": "date",
    "is_backup_to_subfolders": false,
    "is_backup_one_and_send": false,
    "sql_server_local__is_verify_after_backup": false,
    "sql_server_local__is_copyonly": false,
    "sql_server_local__is_ignore_offline_databases": false,
    "sql_server_local__compression_type": "default",
    "sql_server_local__broken_chain_behavior": "warning"
  }
}

Use the following command to import the .json file with the job’s configuration. Note a new backup job will be created on the server where this command is executed.

SqlBak.Job.Cli.exe --import-job --secret-key=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx --file=sqlbak_job_1234.json

The --import-job command has two parameters:

  • --secret-key – can be found on the Download page after you login to sqlbak.com
  • --file– the path to the imported .json job’s configuration file

More information about --import-job command can be found in SqlBak.Job.Cli.exe Reference Manual.

A .json file can be opened via any text editor and can be modified before importing since it contains the settings for a specific backup job that may be incompatible with the new server.

Before importing a .json file to a new server please make sure the connection to a DBMS is set with the same type and name as it set in the original job, or modify it in .json file, specifying the --dbms_type and name that were used during the connection in step 2.

Leave a Reply

Your email address will not be published. Required fields are marked *