SQL Server command line Backup Linux

sqlserver-linux-backup.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
## SQL Server command line backup Linux
## On OpenSUSE 42.2 64Bits
## Author: Nilton OS -- www.linuxpro.com.br
## http://stackoverflow.com/questions/880487/sql-server-command-line-backup-statement
## https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-sqlpackage
## Version: 0.2

### BACKUP SCRIPT SQLSERVER CLI

import os, sys, optparse, socket
import commands, time, re


#from smtplib import SMTP_SSL as SMTP       #secure SMTP protocol (port 465, uses SSL)
from smtplib import SMTP                    #standard SMTP protocol (port 25,587, no SSL)
from email.MIMEText import MIMEText

#---------------------------------------------------------------------------
## esses campos podem ser alterados
user_mssql  = 'sa'
pass_mssql  = 'passwd_sa'
host_mssql  = '127.0.0.1'

hostname    = socket.gethostname()
backup_dir  = '/usr/local/backup/mssql'
data_mssql  = '/var/opt/mssql/data'
log_file    = '/var/log/backup_mssql.log'

l_hostname  = hostname
SMTPserver  = 'smtp.domain.com'
sender      = '[email protected]'
USERNAME    = '[email protected]'
PASSWORD    = 'email_passwd'
destination = ['[email protected]']
des_ccc     = ['[email protected]']
#---------------------------------------------------------------------------
verbose    = False
message    = ''
filestamp  = time.strftime('%Y%m%d_%H%M')



def exec_backup(user_mssql, pass_mssql, host_mssql):
    if not os.path.lexists(backup_dir): os.makedirs(backup_dir)
    sqlcmd = "/opt/mssql-tools/bin/sqlcmd -U%s -P%s -S%s -dmaster -Q" %(user_mssql,pass_mssql,host_mssql)
    # Get a list of databases with :
    db_ls_cmd = "%s \"SELECT NAME FROM sys.sysdatabases WHERE NAME NOT IN ('master','tempdb','model','msdb')\"" %(sqlcmd)
    for database in os.popen(db_ls_cmd).readlines():
        database = database.strip()
        database = re.sub(r'(\(.*\)|NAME|--)', "", database)
        if database:
           filename = "%s-%s.bkp" % (database, filestamp)
           cmd = "%s \"BACKUP DATABASE [%s] TO DISK = N'backups/%s' WITH INIT,NOUNLOAD,NAME=N'%s',NOSKIP,STATS=10,NOFORMAT\"" % (sqlcmd,database,filename,filename)
           log("BACKUP DB: %s FILE: %s" %(database, filename))
           cmd_result = commands.getoutput(cmd)
           log(cmd_result)
           cmd = "mv %s/backups/%s %s/" %(data_mssql,filename,backup_dir)
           commands.getoutput(cmd)
           log("GZIP ON DB=%s FOLDER=%s FILE=%s.gz" %(database,backup_dir,filename))
           cmd = "gzip %s/%s" %(backup_dir,filename)
           commands.getoutput(cmd)
   

def log(mes):
    global message
    str = ("%s - %s\n") %(time.strftime('%b %d %H:%M:%S'), mes)
    message += str
    if verbose: str = str.rstrip("\n"); print str


def send_mail(receiver, Subject):
    global message
    subject   = ("%s JOB: %s HOST: %s") %(Subject, sys.argv[0], hostname)
    receiver  = receiver
    text_subtype = 'plain'

    try:
        msg = MIMEText(message, text_subtype)
        msg['Subject']= subject
        msg['From']   = sender
        msg['To']     = ', '.join(receiver)
        receiver = receiver + des_ccc 

        conn = SMTP(SMTPserver,587,l_hostname)
        conn.set_debuglevel(False)
        conn.login(USERNAME, PASSWORD)
        try:
            conn.sendmail(sender, receiver, msg.as_string())
        finally:
            conn.close()

    except Exception, exc:
        log( "mail failed; %s" % str(exc) ) # give a error message




def clean_files(BackupDir, DaysToKeep):
    now = time.time()
    filelist = [ f for f in os.listdir(BackupDir) if f.endswith(".gz") ]
    for f in filelist:
        f = os.path.join(BackupDir, f)
        if os.stat(f).st_mtime < now - (DaysToKeep * 86400):
            if os.path.isfile(f):
                os.remove(f)
                log('Deleting file: %s with more than %s days ...' %(f, DaysToKeep))



def main():
    global verbose, message
    usage = "usage: %prog --backup [options]"
    parser = optparse.OptionParser(usage)
    parser.add_option("--host", action="store", type="string", dest="HOST_MSSQL", default=host_mssql, help="Entre com o IP do mssql")
    parser.add_option("--user", action="store", type="string", dest="USER_MSSQL", default=user_mssql, help="Username do mssql")
    parser.add_option("--passwd", action="store", type="string", dest="PASS_MSSQL", default=pass_mssql, help="Password do mssql")
    parser.add_option("--clean", action="store", type="int", dest="CLEAN", default=False, help="Para limpar os arquivos X dias")
    parser.add_option("--backup", action="store_true", dest="BACKUP", default=False, help="Para fazer Backup")
    parser.add_option("--debug", action="store_true", dest="DEBUG", default=False, help="Para habilitar Debug")
    parser.add_option("--sendmail", action="store_true", dest="SENDMAIL", default=False, help="Para enviar E-mail")
    options, args = parser.parse_args()
    

    if (options.DEBUG): verbose = True
    

    if (options.BACKUP):
        log("[***JOB BACKUP MSSQL****]")
        exec_backup(options.USER_MSSQL, options.PASS_MSSQL, options.HOST_MSSQL)
        if (options.CLEAN): clean_files(backup_dir, options.CLEAN)
        if (options.SENDMAIL): send_mail(destination, "LOG BACKUP MSSQL")
        salve_log = open(log_file,"w"); salve_log.write(message); salve_log.close()


if __name__ == "__main__":
    main()