How to Backup and FTP SQL Server On Linux

If you are looking for a custom way to backup SQL Server on Linux local database and send the backup to FTP server we can present you the following python-script. If you prefer SAAS solutions try SqlBak that will do everything for you.

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