sqlserver_backup_db_scripts.py

import pyodbc
import time
from shutil import copy
import smtplib
import os
import datetime

def backup_db(cnxn,mode,backup_dir,secondary_backup_dirs,dbname):

    backup_name = dbname+"-"+mode+" database backup"
    waktu = time.time()

    if mode in ["full","diff"] :
        backup_disk = backup_dir+"/"+dbname+"-"+mode+"-"+str(waktu)+".bak"

        diff_option = ""

        if mode == "diff":
            diff_option = " DIFFERENTIAL, "

        sql = f"""
            BACKUP DATABASE [{dbname}] TO  DISK = N'{backup_disk}' WITH {diff_option} NOFORMAT, NOINIT,  NAME = N'{backup_name}', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
            """
    
    elif mode == "trans" :
        backup_disk = backup_dir+"/"+dbname+"-"+mode+"-"+str(waktu)+".trn"

        sql = f"""
            BACKUP LOG [{dbname}] TO  DISK = N'{backup_disk}' WITH NOFORMAT, NOINIT,  NAME = N'{backup_name}', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
            """

    cursor_backup_db = cnxn.cursor()
    #print(sql)
    cursor_backup_db.execute(sql)
    while cursor_backup_db.nextset():
        pass

    # copy backup files 
    for dir in secondary_backup_dirs :
        copy(backup_disk, dir)

def remove_old_backup(dir_to_search,max_hour):
    for dirpath, dirnames, filenames in os.walk(dir_to_search):
        for file in filenames:
            curpath = os.path.join(dirpath, file)
            file_modified = datetime.datetime.fromtimestamp(os.path.getmtime(curpath))
            if datetime.datetime.now() - file_modified > datetime.timedelta(hours=max_hour):
                print("remove "+curpath)
                os.remove(curpath)

#configs 
backup_dir = "C:/temp/backupdb"
secondary_backup_dirs = ["C:/temp/backupdb_1","C:/temp/backupdb_2"]
'''
mode 
1. full
2. diff
3. trans
'''
mode = "trans"
exclude_dbs = []

# database
server = 'tcp:localhost,1433' 
#database = 'master' 
#username = 'myusername' 
#password = 'mypassword' 
#cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

# smtp 
# change 1 to activate
smtp_active = 0
smtp_host = ""
smtp_port = ""
email_sender = "backupsystem@changethis.com"
email_receivers = ['to@todomain.com', 'to2@todomain.com']

# remove old backups 
# change 1 to activate
old_backup_remove = 1 
old_backup_max_hour = 1 #hours



try : 

    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};Server='+server+';Trusted_Connection=yes;')
    cnxn.autocommit = True

    cursor = cnxn.cursor()

    #getting all db 
    sql = """SELECT name
            FROM sys.databases
            WHERE database_id > 4 ;  """

    dbs = cursor.execute(sql).fetchall()

    for db in dbs : 
        if db.name not in exclude_dbs :
            print('processing ' + db.name)
            backup_db(cnxn,mode,backup_dir,secondary_backup_dirs,db.name)

    if old_backup_remove == 1 :
        print("remove old backup")
        # remove old backup in main folder
        remove_old_backup(backup_dir,old_backup_max_hour)

        for dir in secondary_backup_dirs :
            # remove old backup in secondary folder
            remove_old_backup(dir,2 * old_backup_max_hour)


    #close connection
    cnxn.close()

except Exception as e :
    print(e)
    if smtp_active == 1 :

        

        message = f"""
                ERROR backup systems ({str(e)})
                """

        smtp_obj = smtplib.SMTP(smtp_host,smtp_port) 
        smtp_obj.sendmail(email_sender, email_receivers, message)