import pyodbc
import ftplib
import os
import tempfile
import argparse

parser = argparse.ArgumentParser()
parser.add_argument('-sqluser', required=True, type=str,  help='SQL Server user name')
parser.add_argument('-sqlpwd',  required=True, type=str,  help='SQL Server user password')
parser.add_argument('-sqldb',   required=True, type=str,  help='SQL Server database to backup')
parser.add_argument('-ftphost', required=True, type=str,  help='FTP server url (with optional path)')
parser.add_argument('-ftpuser', required=True, type=str,  help='FTP server user name')
parser.add_argument('-ftppwd',  required=True, type=str,  help='FTP server user password')

sql_user = parser.parse_args().sqluser
sql_pwd = parser.parse_args().sqlpwd
sql_db = parser.parse_args().sqldb
ftp_host = parser.parse_args().ftphost
ftp_user = parser.parse_args().ftpuser
ftp_pwd = parser.parse_args().ftppwd

tempfile.mktemp()
bak_file = "%s/%s.bak" % (tempfile.tempdir, sql_db)

try:
    print "Creating a backup of %s to %s..." % (sql_db, bak_file)
    con = pyodbc.connect("DRIVER={ODBC Driver 13 for SQL Server};SERVER=localhost;DATABASE=%s;UID=%s;PWD=%s" % (sql_db, sql_user, sql_pwd), autocommit=True)
    cur = con.cursor()
    cur.execute("BACKUP DATABASE %s TO DISK = '%s'" % (sql_db, bak_file))
    while cur.nextset():
        pass
    con.close()
except Exception as e:
    print "Can't backup '%s': %s" % (sql_db, e)
    exit(1)

try:
    pos = ftp_host.find("/", 0)
    ftp_server = ftp_host[0:pos] if pos > 0 else ftp_host
    ftp_dir = ftp_host[pos:] if pos > 0 else ""

    try:
        print "Connecting to %s..." % ftp_server
        ftp = ftplib.FTP(ftp_server, ftp_user, ftp_pwd)
        if ftp_dir:
            ftp.cwd(ftp_dir)
        with open(bak_file, "rb") as f:
            print "Sending the backup file to %s..." % ftp_host
            ftp.storbinary("STOR %s.bak" % sql_db, f)
            print "Done!"
        ftp.close()
    except Exception as e:
        print "Can't send the backup file to ftp-server '%s': %s" % (ftp_server, e)

finally:
    if os.access(bak_file, os.F_OK):
        print "Removing %s..." % bak_file
        os.remove(bak_file)

To use this script you need to have python 2.7 and usually, it’s already preinstalled on Linux. Also, you will most probably need to install pyodbc library:

sudo apt install python-pip
pip install --upgrade pip
sudo pip install pyodbc

Save this script as, say, sql2ftp.py and run it in the following manner:

sudo python sql2ftp.py -sqluser sa -sqlpwd sa_password -sqldb my_db -ftphost myftp.com -ftpuser ftp_user -ftppwd ftp_password

You need to run it under sudo because it needs to have the rights to remove the backup file created by SQL Server (it’s created under mssql account and an ordinal user can’t delete it).

After the successful run you will see something like this:

Creating a backup of TestDB to /tmp/TestDB.bak...
Connecting to myftp.com...
Sending the backup file to myftp.com...
Done!
Removing /tmp/TestDB.bak...

To solve this problem I took this script and it worked fine for Ubuntu 16.04. Here is the script you need to run to install Microsoft ODBC Driver 13 for SQL Server on Ubuntu:

#!/bin/bash
sudo add-apt-repository ppa:ubuntu-toolchain-r/test
sudo apt-get update

sudo -i export CPPFLAGS="-DSIZEOF_LONG_INT=8"
sudo apt-get -y install gcc-5 g++-5
sudo update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-5 60 --slave /usr/bin/g++ g++ /usr/bin/g++-5

cd ~
echo "Configuring the unixODBC 2.3.1 Driver Manager"
wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.1.tar.gz
tar -xzf unixODBC-2.3.1.tar.gz
cd unixODBC-2.3.1/
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE 1> odbc_con.log 2> make_err.log

echo "Building and Installing the unixODBC 2.3.1 Driver Manager"
sudo make 1> make_std.log 2> make_err.log
sudo make install 1> makeinstall_std.log 2> makeinstall_err.log

cd ~
echo "Downloading the Microsoft ODBC Driver 13 for SQL Server - Ubuntu"
wget "https://meetsstorenew.blob.core.windows.net/contianerhd/Ubuntu%2013.0%20Tar/msodbcsql-13.0.0.0.tar.gz?st=2016-10-18T17%3A29%3A00Z&se=2022-10-19T17%3A29%3A00Z&sp=rl&sv=2015-04-05&sr=b&sig=cDwPfrouVeIQf0vi%2BnKt%2BzX8Z8caIYvRCmicDL5oknY%3D" -O msodbcsql-13.0.0.0.tar.gz
tar -xzf msodbcsql-13.0.0.0.tar.gz
cd msodbcsql-13.0.0.0/

echo "Installing Dependencies"
sudo apt-get -y install libssl1.0.0
sudo apt-get -y install libgss3
sudo echo "/usr/lib64" >> /etc/ld.so.conf
sudo ldconfig

echo "Installing the Microsoft ODBC Driver 13 for SQL Server - Ubuntu"
sudo bash ./install.sh install --force --accept-license

cd ~
echo "Compiling PHP driver"
sudo apt-get -y install php7.0-dev

wget https://github.com/Microsoft/msphpsql/archive/4.0.6-Linux.tar.gz
tar -xzf 4.0.6-Linux.tar.gz
cd msphpsql-4.0.6-Linux/source/pdo_sqlsrv/

phpize && ./configure CXXFLAGS=-std=c++11 && make
sudo make install

echo "Enabling Driver for php7.0-fpm and CLI"
sudo bash -c 'echo -e "; configuration for php common module \n; priority=10 \nextension=pdo_sqlsrv.so" > /etc/php/7.0/mods-available/pdo_sqlsrv.ini'
sudo ln -s /etc/php/7.0/mods-available/pdo_sqlsrv.ini /etc/php/7.0/fpm/conf.d/20-pdo_sqlsrv.ini
sudo ln -s /etc/php/7.0/mods-available/pdo_sqlsrv.ini /etc/php/7.0/cli/conf.d/20-pdo_sqlsrv.ini

cd ~
echo "Cleaning up.."
sudo rm -rf msphpsql-4.0.6-Linux/
sudo rm -rf unixODBC-2.3.1/
sudo rm -rf  msodbcsql-13.0.0.0/
sudo rm 4.0.6-Linux.tar.gz
sudo rm msodbcsql-13.0.0.0.tar.gz
sudo rm unixODBC-2.3.1.tar.gz

echo "All done, please be sure to test via the CLI & restart php7.0-fpm once complete."
php -i | grep 'PDO drivers'

Save this script to a file (say installodbc.sh), add the execute attribute, and run it:

nano installodbc.sh
chmod +x installodbc.sh
sudo ./installodbc.sh

Prerequisites

• Microsoft Azure account;
• SSH terminal.

How to Deploy Azure Linux Virtual Machine with SQL Server

The following is a step-by-step procedure.

Step 0. Virtual Machine Selection

Get into your Microsoft Azure account.

Click on “New” as shown above, then see a menu as shown below.

As shown above click on “Compute” section and look for “SQL Server Linux”.

You should see a list of available SQL servers, as shown below (it may be a long list, so be patient).

Click on “Free SQL Server License: SQL Server 2017 Developer on Ubuntu Server 16.04 LTS“.

You should see something like the following:

Change “Resource Manager” to “Classic” (see the red arrow that points out to the place on the picture above), this is just to make your life a little bit easier.

To finish the preparations click on the “Create” button meaning “Create Azure SQL Server 2017 Developer on Ubuntu” and start the following procedure of creation.

Step 1. Starting the Creation Procedure with Basics

Fill in the fields Name, User name, Password, etc.

Step 2. Choosing the Size of Your Linux Virtual Machine

Choose the size of your SQL server on Ubuntu.

Pricing here is not for the case, it is for other cases.

Click on “Select” button.

Step 3. Configure Optional Features

See the parameters there and change them, if you want, then click on “OK” button.

Step 4. See the Summary and check the options, names, etc

Click on “OK” button.

Note: refreshing the screen sometimes helps when you wait too long for finishing the procedure

Now you should have Microsoft Azure SQL server on Ubuntu at your disposal (see the Overview of the SQL server on Ubuntu below).

How to Connect to Azure SQL Server on Linux (Ubuntu)

Connect SSH Terminal to Ubuntu Server (Ubuntu Virtual Machine)

Take parameters for the SSH terminal connection to Linux (Ubuntu) server from the parameters list of your Ubuntu virtual machine (Ubuntu server) created according to the creation procedure described above.

For example, take “DNS name” from your Azure “SQL Server on Ubuntu” screen shown at the very last picture of the creation procedure and copy it to the “Host” field in the Xshell New Session Properties window, if you use Xshell terminal.

 Configure SQL Server on Ubuntu using the SSH Terminal

Type the following Microsoft instructions into your SSH terminal window:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

sudo apt-get update

After the last command (“update”) there should be a long list like the following:

Continue typing:

sudo apt-get install -y mssql-server

sudo /opt/mssql/bin/mssql-conf setup

systemctl status mssql-server

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"

sudo apt-get update

After entering the last command (“update”) there should be a long list similar to one from the above black-and-white output window.

Continue typing:

sudo apt-get install -y mssql-tools unixodbc-dev

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

sudo /opt/mssql/bin/mssql-conf setup

After entering the last (“setup”) command from the list you will be asked for two things.

The first thing: choose an edition of SQL server.

Choose your option (Developer for this case).

The second thing: enter the SQL Server system administrator password.

Type in the password (you will use it later).

Note: although it doesn’t say it explicitly, the password here must contain both uppercase and lowercase letters, numbers, and other (non-alphanumeric) symbols, 8 characters is the minimal length for the password. 

Now the connection to the SQL server on Ubuntu should work.

Check its version just in case to make sure that the server is really up and running.

SELECT @@VERSION

As a result, you should see a text like this:

From now on the SQL server on Linux (Ubuntu) should be ready to use. 

Test the SQL Server on Linux (Ubuntu)

Use sqlcmd command-line tool to use Microsoft Azure SQL Server on Linux (Ubuntu).

Note: sqlcmd tool has been installed during configuring the SQL Server. 

sqlcmd -S localhost -U SA -P '<SQL Server system administrator password>'

Note: <SQL Server system administrator password> in the line above is the same “SQL Server system administrator password” that you defined above when answering about “the second thing”. 

If successful, you should get to a sqlcmd command prompt from the Microsoft Azure SQL Server on Ubuntu:

1>

Note: the prompt changes (incrementing) each time after entering a command, and the counting (incrementing) stops after entering “GO” (after that the counting starts from “1>” again). 

Microsoft site contains a set of instructions any user can use for testing purposes, let’s use it:

CREATE DATABASE TestDB

SELECT Name from sys.Databases

GO

After this you should see a text like this:

Let’s proceed further:

USE TestDB

CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)

INSERT INTO Inventory VALUES (1, 'banana', 150);

INSERT INTO Inventory VALUES (2, 'orange', 154);

SELECT * FROM Inventory WHERE quantity > 152;

GO

Note: DON’T FORGET ABOUT “GO” AT THE END.

As a result, you should see a text like this:

That’s it

Have a good time using the Microsoft Azure SQL Server on Linux (Ubuntu)

As in our PHP and Node tutorials, we’ll assume that you’re running Ubuntu and that you’ve got most of the prerequisites installed. For Python, we’re going to use the industry-standard Python 3 syntax, at the latest version, 3.7. If you’re running a different operating system or different Python version, you may have to change a few things but the gist should be the same.

Install PyODBC

Once again, there’s very little in the way of prerequisites here. We’re using pyodbc, which you can install via pip:

pip install pyodbc

Other common libraries, such as the popular SQLAlchemy, can be used on Linux too and provide a complete ORM experience if that’s more up your alley.
Build A Connection
PyODBC is a little finnicky in how you connect to a database: you have to build the connection string yourself. I find it’s easiest to create a dictionary and use Python 3’s string formatting to do so:

import pyodbc

details = {
 'server' : 'localhost',
 'database' : 'MyDB',
 'username' : 'me',
 'password' : 'myPass'
 }

connect_string = 'DRIVER={{ODBC Driver 13 for SQL Server}};SERVER={server};PORT=1443; DATABASE={database};UID={username};PWD={password})'.format(**details)

connection = pyodbc.connect(connect_string)
print(connection)

Put that in a .py file and run it, and you should see the connection object information printed out in your terminal. If you’re getting an error, make sure you’ve correctly copied the syntax for the connection string, including the double braces around the driver name. Those tell the formatting function that the insides of the braces aren’t variable names, they’re part of the connection string itself.

Running Database Queries

After you establish a connection, pyodbc makes it easy for you to grab a cursor and begin running queries. The cursor class implements Python’s magic “state managers”, which means you can use it in a with-clause and wait on the results. This makes running queries much easier: you just put them in a with-clause and you don’t have to worry about closing the cursor.

As in our other examples, we’ll assume you’ve got a database with some basic employee data and do some simple manipulations to it:

#grab a new cursor object from the connection
cursor = connection.cursor()

tsql = "INSERT INTO ExampleSchema.Employees (Name, Site) VALUES (?,?);"
#we can use the cursor’s with-clause syntax with the values
with cursor.execute(tsql,'Donovan','Satellite Work Center'):
  print ('Successful Insertion!')

Other statements are just as simple. To read the data back out, we use the same basic structure and read out the cursor’s results from inside the with-clause:

tsql_select = "SELECT Name, Site FROM ExampleSchema.Employees;"
with cursor.execute(tsql_select):
  row = cursor.fetchone()
  while row:
    print (str(row[0]) + " works at " + str(row[1]))
    row = cursor.fetchone()

And updates and deletes are done in a similar fashion.

Python Best Practices

Because pyodbc makes extensive use of the while-clause structure, you may want to review that syntax to make sure you know when your cursors are opening and closing. In fact, the same syntax can be used for the connection itself, but having multiple nested layers of with-clauses is not very Pythonic. Instead, if you need to close the connection manually, you can use connection.close().

Make sure you’ve read our installation guide before we begin. The guide will assume you’ve got a working installation of MS SQL Server on Ubuntu. Other Linux variants should be similar, but you should double-check for differences in how commands are run.

Setting Configuration with mssql-conf

If you’re running SQL Server directly on your Linux machine, then the recommended way to change configuration is through the mssql-conf binary that was installed alongside your SQL Server installation.

As a simple example, we’ll look at changing the port that SQL Server runs on. By default, the port is set to 1433 but you may want to change it for security reasons. For example, we’ll set it to 14330. To change the port, start by running mssql-conf with the new port number:

sudo /opt/mssql/bin/mssql-conf set network.tcpport 14330

You should get back a message indicating the port has been updated. Now, you’ll need to restart the server so the configuration changes can take effect:

sudo systemctl restart mssql-server

The next time you connect to the server, you’ll need to use the updated port number. For a typical configuration change, that’s all that needs to happen. Using the mssql-conf binary, you can change everything from the log or data directory, to the high availability settings. You can run mssql-conf list to get a complete listing of commands to run, or run man mssql-conf to see the complete guide to the config binary.

Setting Configuration through Environment Variables

Using mssql-conf is great when you’re running SQL Server locally, but often times it’s not possible to execute commands directly on a database server. This is especially true in a containerized setting, like when running Docker. There, it can be helpful to set important configuration values through the environment, which can be manipulated in a container setting.

As above, we can change the configuration to make SQL Server run on a different port, port 14330. This time, we set an environment variable to make the change. Add the following to your Dockerfile before the line running SQL Server:

ENV MSSQL_TCP_PORT 14330

And then run docker build to build a new container with the different port. Make sure that you allow access on the new port, and that you restart the container with the new environment variable, and you should see SQL Server running with the new configuration on the new port.

As before, several important configuration values can be set using the environment, including data directories, high availability, and even the initial installation password. While it’s possible to use either mssl-conf or environment variables to configure SQL Server on Linux, it’s best to use mssql-conf if you can. That allows you to easily see what the existing configuration is, and change it in a central place.

If you haven’t already done so, you’ll want to take a look at our installation instructions to get your development environment ready. This guide will assume you’re running Ubuntu, but any Linux flavor will do. We’ll also assume you have node and npm binaries installed.

Install NPM Dependencies

First, you’ll need to install some npm libraries for interacting with SQL Server. I recommend tedious, which is a T-SQL only driver that Microsoft supports:

npm install tedious

Tedious works with version 4 of Node.js and later.  The tedious library is actually all you need to execute T-SQL commands. Other libraries, like the popular Sequelize library, can interact with MS SQL as well and provide a more full-featured ORM.

Test the Connection

A basic connection with tedious is fairly simple. You take the connection details and pass them to the connection constructor. Connections implement the EventEmitter API, which means you can wait on the asynchronous “connected” event to fire before continuing:

let tedious = require(‘tedious’);
let Connection = tedious.Connection;
const config = {
userName: 'myUser', // update me
password: 'myPass', // update me
server: 'localhost',
options: {
    database: 'MyDB'
  }
}

function handleConnection(err){
  if(err) console.error("error connecting :-(", err);
  else console.log("successfully connected!!")
}
let connection = new Connection(config);
connection.on('connect', handleConnection);

Our handleConnection function lets us asynchronously handle errors and notify us once we’ve connected. Put the above code in a module called connect.js and run it by typing node connect.js in a terminal. You should see “successfully connected!” pop up in your window.

Run Some Queries

Now that we’ve got a successful connection, we can use that as a basis for running more complicated queries. In tedious, these are done by creating new Request objects, and then calling a method to fill in the parameters. When the Request is executed and returns, it calls a callback that we provide.

As with our PHP example, we’ll assume you’ve got a basic employees table in your database. Here’s a simple select statement you can run with your connection from the previous section:

const statement = "SELECT Id, Name, Location FROM ExampleSchema.Employees;"
function handleResult(err, numRows, rows){
  if(err) return console.error("error running select statement", err);
  console.log("got", numRows, "rows back from the DB");
}

let results = [];
function handleRow(columns){
  columns.forEach(function(column){
    if(column.value !== null) results.push(column.value);
  });
}

let request = new tedious.Request(statement, handleResult);
request.on('row', handleRow);
connection.execSql(request);

Here we see the basic principles of MS SQL running in Node.js: everything is done asynchronously by creating requests and passing them to tedious to execute. The results are also events, and we use the EventEmitter “on row” syntax to build up the results.

Using the same paradigm we can also execute INSERT or UPDATE commands:

const statement = "INSERT INTO ExampleSchema.Employees OUTPUT INSERTED.Id (Name, Site) VALUES (@Name,@Site)"
const sampleUser = {name : "Joan", site : "Downtown"};
let request = new tedious.Request(statement, handleResult);
request.addParameter('Name', tedious.TYPES.NVarChar, sampleUser.name);
request.addParameter('Site', tedious.TYPES.NVarChar, sampleUser.site);
connection.execSql(request);

Best Practices

Tedious makes heavy use of the EventEmitter pattern, so you should be familiar with that before using the library in your code. In particular, you need to make sure that you’re not trying to execute statements before the connection is ready, or trying to read results before they’ve been written.

We’re assuming you’ve already got MS SQL Server running on an Ubuntu machine. If you haven’t, take a look at our installation guide to get up to speed. We’ll be using Ubuntu, but the instructions aren’t that much different for RHEL or others.

Install PHP Extensions

Using PEAR, you’ll need to install and configure a few extensions for PHP to be able to access the MS SQL Server. PHP comes with the PHP Data Objects interface that provides a common way to access many different databases, but you need to install an extension for MS SQL Server to be able to use it PDO.

To install them, run the following PEAR command:

sudo pecl install sqlsrv pdo_sqlsrv

Then configure PHP to use the new extensions using bash (note the sudo!):

sudo echo "extension= pdo_sqlsrv.so" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`
sudo echo "extension= sqlsrv.so" >> `php --ini | grep "Loaded Configuration" | sed -e "s|.*:\s*||"`

Connect to the Database

Now that PHP’s PDO knows how to interact with MS SQL Server, you can use it to connect to the server and run database commands. To establish a connection, use the global $sqlserv_connect function. For example, to connect to a local server:

<?php

//format: serverName\instanceName, portNumber (default is 1433)
$serverName = "localhost";

$connectionInfo = array( "Database"=>"dbName", "UID"=>"myUserName", "PWD"=>"myPassword");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Got a connection!<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
?>

Run that on the command line and you should see “Got a connection!” indicating that you were successfully connected to the database. The hard part is over! All that’s left is running some commands on the connection.

Execute Queries

Let’s assume you’ve got a simple SQL table set up to track employees in your organization, with fields for employee name, id, and work site. You’d like to run INSERT and UPDATE queries to add employees and change their data as part of your web application. Once you’ve got the connection set up as above, you can execute any command you’d like. To insert new users:

$tsql= "INSERT INTO ExampleSchema.Employees (Name, Site) VALUES (?,?);";
$params = array('Beverly','Remote Office');
$getResults= sqlsrv_query($conn, $tsql, $params);
$numRows = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $numRows == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ( "inserted ".$numRows." row(s) " . PHP_EOL);

Notice how we use the $conn object we created above, and pass it to the global function sqlsrv_query. We also pass in a T-SQL statement and the associated parameters. Once the statement is executed, you also need to check to make sure you got a valid result.

Updates work the same way. We can change just the $tsql statement and get a working update:

$tsql= "UPDATE ExampleSchema.Employees SET Site= ? WHERE Name = ?);";
$params = array('Undersea Research Site', 'Beverly'); //etc.

Best Practices

Now that you’ve got a running application, be sure to check out the documentation on PDO. In particular, you’ll want to confirm after each statement that you execute that you got a correct result and that you got no errors. If you’re having trouble with your MS SQL Server on Linux, the “print_r( sqlsrv_errors(), true)” statement can be very helpful in debugging.

As the PHP driver is relatively young, you might also want to check out the GitHub page and the official documentation for new releases and for explanations of how to work around known issues.

Backing Up Windows Data

If you’re using SQL Server Management Studio, making a backup is as simple as selecting it from the appropriate menu. Find your database in the Object Explorer, right click it, and select Tasks → Back Up Database. Use the backup menu to create a full database backup and save it to an appropriate location.

If you’re not using Management Studio, you’ll need to make the backup by using the TSQL BACKUP DATABASE command. The syntax is straightforward:

BACKUP DATABASE MyData  
TO DISK = 'Z:\WindowsBackups\MyData.bak'  
   WITH FORMAT,  
      MEDIANAME = 'Z_WindowsBackups',  
      NAME = 'Windows Database';

In both cases, you should end up with a .bak file generated by your Windows database in a location that you control. The .bak file contains the entire contents of your database, so make sure you treat it securely!

Transferring the Backup to the Linux Server

Now that you’ve got the backup file, the next step is to get in on the Linux machine that’s running SQL Server. The standard way to achieve this is via scp, a tool for transferring files to remote machines over a network. While Windows doesn’t come with scp installed, you can install PuTTY and use it transfer the files.

Once you’ve got PuTTY installed and established a connection to the Linux server, the command to transfer the files is again very straightforward:

pscp Z:\WindowsBackups\MyData.bak user@linux_host:MyData.bak

Where “linux_host” is the address of your Linux server. The command stores the backup file in the home directory of the remote server, under the same name.

Next, using PuTTY, you’ll need to ssh into the remove server and move the file to a location where we can restore it from. With SQL Server for Linux, the restore file needs to be in a subdirectory of the main SQL Server directory of /var/opt/mssql. Once you’re ssh’d in, you can create the backup subdirectory:

mkdir -p /var/opt/mssql/backup

Then you can move the .bak file into that directory:

mv  MyData.bak /var/opt/mssql/backup/MyData.bak

Restoring From the Backup File

Finally, to get the data onto the Linux SQL instance, you’ll need to use the .bak file to restore the data. Run the following command to get into a SQL shell:

sqlcmd -S localhost -U my_username

From there, all you need to do is run the RESTORE DATABASE command and pass in the filename you chose:

RESTORE DATABASE MyData
FROM DISK = '/var/opt/mssql/backup/MyData.bak';

That’s it! From there, you should be able to query the Linux server exactly as you could the Microsoft one.

• by using SqlBak that will do everything for you
• by running the sqlcmd client directly on your Linux machine (this will be described in this post below)
• by running a custom python script

This post describes how to backup SQL Server on Linux manually via a command line. To begin with, make sure that you have successfully installed mssql-tools, the package that includes sqlcmd. Then start your sqlcmd client as follows:

sqlcmd -H localhost -U SA

This connects to the system administrator account on the databases that are local to your server. You will then be prompted for a password (if you don’t recall your password, note that you may have set it up when you installed SQL Server initially, specifically when you ran mssql-conf setup).

Next, at the sqlcmd client prompt, enter the following on successive lines:

BACKUP DATABASE [yourdbname] TO DISK = ‘yourdbname.bak’
GO

This will perform a full backup of the database that you specify (“yourdbname”) to /var/opt/mssql/data, and will give it the name “yourdbname.bak.” You will see the progress of the backup, and you can, of course, change the target location (it could be to the Azure cloud, for example, by substituting the command TO URL for TO DISK). There are also multiple options that you can specify. For example,

BACKUP DATABASE [yourdbname] 
       TO DISK = ‘yourdbname.bak’,
          DISK = ‘yourdbname.bak2’,
          DISK = ‘yourdbname.bak3’
          WITH STATS = 10
GO

will divide your backup into three files of roughly the same size, and will log to the terminal each time 10% of the backup is successfully completed. Note that sub-statements of the main clauses (the main clauses here are TO and WITH) generally require termination by commas, whereas the main clauses themselves do not. You can explore additional backup options, such as compression, media set parameters, and log-related choices, by having a look at the Microsoft documentation on the topic.

Another common need for database administrators is to perform a differential backup, rather than the default full backup (a differential backup only includes data that has been added since the last full backup). To run a differential backup, you can use the following command:

BACKUP DATABASE [yourdbname] 
       TO DISK = ‘yourdbname.bak’
       WITH DIFFERENTIAL
GO

 After performing the differential backup, you should check your file sizes with

ls  -lah

in order to see whether they make sense as partial data (however, needless to say, a differential backup could be larger than a full backup if a lot of data was added since the last full backup).

Finally, note that it is possible to start up the sqlcmd client and execute your backup commands all in one statement by using the “Q” option with sqlcmd as follows:

sqlcmd -H localhost -U SA -Q "BACKUP DATABASE [yourdbname] TO DISK = 'yourdbname.bak' WITH STATS = 10"

That’s it.

First of all, make sure that have mssql-tools installed. If you are not sure you can simply try to install it:

sudo apt-get install mssql-tools

and if you already have it installed you will get something like this:

Reading package lists... Done
Building dependency tree 
Reading state information... Done
mssql-tools is already the newest version (14.0.3.0-1).
0 to upgrade, 0 to newly install, 0 to remove and 242 not to upgrade.

Otherwise, it will be installed on your system. When everything is installed you may still get “sqlcmd: command not found” error when you try to run sqlcmd if you have no proper symlinks created.

First of all, let’s see how this tool is called on your system. It might be called something like sqlcmd-13.0.1.0 or simply sqlcmd but it should be in /opt/mssql-tools/bin/ directory. Let’s see what we have there:

sudo ls /opt/mssql-tools/bin/sqlcmd*

After you get the name of the tool you can create a symlink:

sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd

That’s it.

P.S. Here is a free service to backup SQL Server on Linux.