Link Search Menu Expand Document
Sign Up

SqlBak-CLI documentation

SQL Server samples

Below are examples of commands and configuration files for the SqlBak-CLI utility:

1. Backup three databases, and transfer them to a Linux server via SSH (SFTP)

Command to run a backup

sqlbak-cli run-backup --job-settings=backup-settings.json

Backup settings

{
	"source":{
	  "type":"mssql",
	  "data_source":".",
	  "trust_server_certificate":true,
	  "user_name":"sa",
	  "user_password":"********",
	  "databases": [
			"wordpress",
			"logs",
			"market"
		]
	},
	"storages":[
	  {
		 "type":"sftp",
		 "server_name":"backup-storage.com",
		 "user_name":"Pranastest",
		 "user_password":"*************",
		 "path":"backups"
	  }
	]
}

Command to run a restore

sqlbak-cli run-restore --job-settings=restore-settings.json

Restore settings

{
	"type": "restore",
	"target": {
	  "type":"mssql",
	  "data_source":".",
	  "trust_server_certificate":true,
	  "user_name":"sa",
	  "user_password":"********",
	  "databases": [
		  {
			  "source_name": "wordpress",
			  "target_name": "wordpress"
		  },
		  {
			  "source_name": "logs",
			  "target_name": "logs"
		  },
		  {
			  "source_name": "market",
			  "target_name": "market"
		  },
	  ],
	},
	"storage": {
		 "destination_type":"sftp",
		 "server_name":"backup-storage.com",
		 "user_name":"Pranastest",
		 "user_password":"*************",
		 "path":"backups"
	}
}

2. Backup all databases and send them to Backblaze storage

Command to run a backup

sqlbak-cli run-backup --job-settings=backup-settings.json

Backup settings

To backup all the available databases there is no need to list all of them, instead specify mssql_custom and the application will backup all databases.

{
   "source":{
      "type":"mssql",
      "source_name":"source-name",
      "data_source":".",
      "trust_server_certificate":true,
      "is_integrated_security":false,
      "user_name":"sa",
      "user_password":"*******",
      "database_types": [ "mssql_custom" ]
   },
   "storages":[
      {
         "type":"backblaze_b2",
         "bucket_name":"backups-bucket",
         "access_key_id":"1234abcd56413b500z0g00t0f",
         "access_key":"aes:zdeDwR7WxFQY91neR1/a9xTrH0dSOVrGmShJ1PkM8WA=",
         "path":"database-backup"
      }
   ]
}

Command to run a restore

sqlbak-cli run-restore --job-settings=restore-settings.json

Restore settings

There is no way to restore all the backups at once. The backups that should be restored should be listed.

{
   "type": "restore"
   "target":{
      "type":"mssql",
      "source_name":"source-name",
      "data_source":".",
      "trust_server_certificate":true,
      "is_integrated_security":false,
      "user_name":"sa",
      "user_password":"*******",
      "databases": [
          {
              "source_name": "wordpress",
              "target_name": "wordpress"
          },
          {
              "source_name": "logs",
              "target_name": "logs"
          },
          {
              "source_name": "market",
              "target_name": "market"
          },
      ]
   },
   "storage":
      {
         "type":"backblaze_b2",
         "bucket_name":"backups-bucket",
         "access_key_id":"1234abcd56413b500z0g00t0f",
         "access_key":"aes:zdeDwR7WxFQY91neR1/a9xTrH0dSOVrGmShJ1PkM8WA=",
         "path":"database-backup"
      }
   
}

3. Full and differential backups of two databases and upload backups to FTP without compression

Command to run full backup

sqlbak-cli run-backup --job-settings=backup-settings.json

Command to run differential backup

To run a differential backup, the following parameter --backup-type should be specified:

sqlbak-cli run-backup --job-settings=backup-settings --backup-type=diff

Backup settings

Settings for full and differential backups are set in one file. However, in the keep_settings section, a different retention period for different types of backups can be specified.

In the example below, full backups will be deleted if they are older than 120 hours, and differential backups will be deleted after 10 hours.

{
   "source": {
      "type":"mssql",
      "data_source":".",
      "trust_server_certificate":true,
      "user_name":"sa",
      "user_password":"******",
      "databases":[ "DataOne", "MediaOne" ]
   },
   "compression_settings": {
      "is_enabled":false
   },
   "storage": {
      "type":"ftp",
      "server_name":"210.123.321.56",
      "user_name":"Mike",
      "user_password":"aes:9W6w/x8gSASDOI3ILLSADSDASji29nIlnUS12a0cZCIJMPIUSDIJ=",
      "path":"ASP-XPS",
      "keep_settings":[
         {
            "backup_type":"full",
            "hours":120
         },
         {
            "backup_type":"differential",
            "hours":10
         }
      ]
   }
}

Command to run a restore

sqlbak-cli run-restore --job-settings=restore-settings.json

Restore settings

To restore a differential backup, SqlBak-CLI will download both the needed full backup and the differential backup, and then restore them sequentially.

{
   "type": "restore",
   "source":{
      "type":"mssql",
      "data_source":".",
      "trust_server_certificate":true,
      "user_name":"sa",
      "user_password":"******",
      "databases": [
          {
              "source_name": "wordpress",
              "target_name": "wordpress"
          }
      ]
   },
   "storage": {
      "type":"ftp",
      "server_name":"123.223.221.56",
      "user_name":"Mike",
      "user_password":"aes:9W6w/x8gSA213I3ILLSADSDASji29nIlnUS12a0cZ41MPIUSDIJ=",
      "path":"backups"
   }
}

4. Using an emergency backup storage

Command to run a backup

sqlbak-cli run-backup --job-settings=backup-settings.json

Backup settings

In this example, a backup will be sent to SFTP (over SSH). If an error occurs during the transfer, the backup will be saved in the local folder.

{
        "source" : {
                "type": "mssql",
                "source_id": "source-id",
                "source_name": "source-name",
                "data_source": ".",
                "trust_server_certificate": true,
                "is_integrated_security": false,
                "user_name": "sa",
                "user_password": "**********",
                "databases": [ "DataOne", "MediaOne" ]
        },
        "compression_settings" : {
                "is_enabled": true
        },
        "storages" :
                [
                        {
                                "type": "sftp",
                                "server_name": "backup-server.com",
                                "server_port": 22,
                                "user_name": "pranas",
                                "authentication_method": "public_key",
                                "user_password": "",
                                "ssh_private_key": "",
                                "timeout": 5000,
                                "max_transfer_speed": null,
                                "use_large_buffers": false,
                                "keep_alive_during_transfer_interval": null,
                                "path": "backups"
                        },
                        {
                                "type": "folder",
                                "path": "/tmp/object",
                                "user_name": null,
                                "user_password": null,
                                "keep_settings": [
                                        { "backup_type": "full", "hours": 120 },
                                        { "backup_type": "differential", "hours": 10 }
                                ],
                                "is_emergency" : true
                        }
                ]

}

Command to run a restore

sqlbak-cli run-restore --job-settings=restore-settings.json

Restore settings

{
        "job_type" : "restore",
        "target" : {
                "type": "mssql",
                "source_id": "source-id",
                "source_name": "source-name",
                "data_source": ".",
                "trust_server_certificate": true,
                "is_integrated_security": false,
                "user_name": "sa",
                "user_password": "**********",
                "databases": [
                    {
                        "source_name": "wordpress",
                        "target_name": "wordpress"
                    }
                ]
        },
        "storage" :
                        {
                                "destination_type": "sftp",
                                "server_name": "backup-server.com",
                                "server_port": 22,
                                "user_name": "pranas",
                                "authentication_method": "public_key",
                                "user_password": "",
                                "ssh_private_key": "",
                                "timeout": 5000,
                                "max_transfer_speed": null,
                                "use_large_buffers": false,
                                "keep_alive_during_transfer_interval": null,
                                "path": "backups"
                        }
}

5. Using GFS Backup Storage Strategy

To implement the GFS (Grandfather-Father-Son) strategy, three backup types should be performed:

FULL

Differential (DIFF)

Transaction Log (LOG)

In the keep_settings section, different intervals for storing each backup type should be specified:

FULL backups are stored for 180 days

DIFF backups are stored for 30 days

LOG backups are stored for 7 days

Set a backup schedule with different intervals for each backup type. CRON tasks for Linux or Task Scheduler for Windows can be used to set a backup schedule:

FULL every 1st of the month:

sqlbak-cli run-backup --job-settings=backup-settings.json -–backup-type=full

DIFF once a week (e.g. on Saturdays)

sqlbak-cli run-backup --job-settings=backup-settings.json -–backup-type=differential

LOG backup once a day

sqlbak-cli run-backup --job-settings=backup-settings.json -–backup-type=inc
{
   "source" : {
      "type":"mssql",
      "data_source":".",
      "trust_server_certificate":true,
      "user_name":"sa",
      "user_password":"******",
      "databases": [ "DataOne" ]
   },
   "compression_settings" : {
      "is_enabled":false
   },
   "storages" : [ {
         "type":"ftp",
         "server_name":"210.123.321.56",
         "user_name":"Mike",
         "user_password":"aes:9W6w/x8gSASDOI223ILLSADSDASji29nIlnUS12a0cZCIJMPIUSDIJ=",
         "path":"backups",
         "keep_settings":[
            {
               "backup_type":"full",
               "hours": 180
            },
            {
               "backup_type":"differential",
               "hours": 30
            },
            {
               "backup_type":"inremental",
               "hours": 7
            }
         ]
      }
   ]
}