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

• 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.

Here how it looks like:

Reading package lists... Done
Building dependency tree 
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
mssql-tools : Depends: libstdc++6 (>= 5.2) but 4.9.2-10ubuntu13 is to be installed
E: Unable to correct problems, you have held broken packages.

If you found yourself in such a situation then probably your Ubuntu is a bit old. SQL Server on Linux supports at least Ubuntu 16.04 so the best solution here to upgrade your OS.

If you are unsure about your Ubuntu’s version you can run the following command in a terminal:

lsb_release -a

or check it in System Settings -> Details if you prefer GUI.

When bringing SQL Server to Linux the IT giant Microsoft made sure to bring to Linux the performance, scale value and the full functional of the SQL Server RDBMS. And by doing that they made sure that the applications that performed well in SQL Server on Windows are equally good in SQL Server on Linux. For that purpose, Microsoft development team made a decision to marry parts of the Microsoft Research (MSR) project Drawbridge with the existing platform layer of SQL Server called SQL Server Operating System (SOS) with the goal of creating the SQLPAL that ultimately made possible the well-crafted SQL Server outside Windows.

As far as the Drawbridge project is concerned, it has provided abstraction between the underlying operating system and the application which ensured the secure containers, while SOS provided robust memory management, IO services and thread scheduling. Through the creation of SQLPA Microsoft developers made possible to use the existing Windows dependencies on Linux with the help of parts of the Drawbridge design focused on OS abstraction while leaving to SOS the key OS services. Moreover, they have changed the SQL Server database engine code to call directly into SQPAL for resource intensive functionality instead of Windows libraries.

Model of the Process

The diagram below demonstrates what the address space looks like when running. Here, the host extension is merely a native Linux application. Upon starting the host extension loads and initializes SQLPAL, SQLPAL and then brings up SQL Server. In addition, the team made sure that SQLPAL is able to launch software isolated processes that are merely a collection of threads and allocations running within the same address space. This is used for such things as SQLDumper, an application that is run when SQL Server encounters a problem to collect an enlightened crash dump.

Yes, at first look some users may think that this diagram shows a lot of layers, but in reality, there are no hard boundaries between SQL Server and the host.

Advancement of SQLPAL

When the project began, SQL Server was built on SOS and Library OS was independent. The ultimate goal set by Microsoft team is to have a merged SOS and Library OS as the core of SQL PAL.  When it was released initially for public preview, this merge wasn’t fully completed, but the heart of SQLPAL had been replaced with SOS.  For instance, instead of the original Drawbridge implementations the threads and memory already use SOS functionality.

The outcome of that is that there are two instances of SOS running inside the CTP1 release: one in SQL Server and one in SQLPAL. The reason it still works perfectly fine is that the SOS instance in SQL Server is still using Win32 APIs which call down into the SQLPAL. And instead of Win32 now SQLPAL instance of the SOS code calls the host extension ABIs (i.e. the native Linux code).

Currently, Microsoft is working on removing the SOS instance from SQL Server by exposing the SOS APIs from the SQLPAL. When this is going to be completed is still unclear but as soon as they do it everything will flow through the single SQLPAL SOS instance.

On November 16, 2016 Microsoft released the first public preview of SQL Server for Linux. And, instead of porting SQL Server to run as a native Linux application, the company chose to use its Drawbridge application sandboxing technology.

Newly introduced SQL Server for Linux runs atop a Drawbridge Windows library OS, a user-mode NT kernel, within a secure container called a picoprocess. This secure container communicates with the host Linux operating system through the Drawbridge application binary interface.

This means that Microsoft’s SQL Server for Linux is actually the Windows SQL Server that is executable with a reduced Windows 8 kernel glued beneath, all running in an ordinary Linux process.

You can find drawbridge references in an 8.4MB library located at /opt/mssql/lib/system.sfp. The /opt/mssql/bin/sqlservr binary is actually a loader. After closely studying the code it was found that the components are brought to a sandbox and then integrity checks and whitelisting are performed to make sure that it is allowed to do so. After that it runs the contained SQL Server executable.

It is expected that SQL Server on Linux will be fully available by mid-2017. Moreover, it is planned that it will support multiple Linux distributions. So far, the preview version is available only for Ubuntu and Red Hat. Users are also able to download the preview version as a Docker image.