MS SQL Backup with Python and pyodbc

Hello readers, i have been in a situation where i wanted to take backup from a MS SQL database through the network and i didnt have any clue about it, so is started digging the internet for a solution, which came up quickly at “ryan’s tech blog” link on how to actually take the backup, after this i realised that there were multiple databases and i want all of them, of course these databases created/deleted at will so the next problem was how to get the complete list of the current databases in the instance, so i came up with the below 2 functions using the pyodbc module.

The Concept:
There is a windows server with MS SQL installed and file sharing enabled.
There is a windows client in the network which connects to MS SQL though “trusted connection” AKA no user/password needed to connect to database because this is handled by the windows authentication mechanism, and also it has mounted a network drive from the windows server’s shared folder.

The Procedure:
The windows client connects to the Database and takes the databases list, then it commands the database to take backup to a local directory, then take the backup data through netword drive and copy them wherever you want.

The Code:

# imports
import pyodbc

# define the backup paths
server_backup_path = 'c:\\mssql_backup\\'
client_backup_path = 'z:\\mssql_backup\\'

# Connection object (notice that i dont include the database name)
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=SERVER\\DATAINSTANCE;Trusted_Connection=yes', autocommit=True)

# List databases function
def list_databases(conn_obj):
  dbs = []
  cur = conn_obj.cursor()
  result = cur.execute('SELECT name from sysdatabases').fetchall()
  cur.close()
  for db in result:
    dbs.append(db[0])
  return dbs

# backup database function, please notice that the function gets 2 paths, one from the server's point of view
# and one from the clients point of view aka network drive
def backup_db(conn_obj, db_name, server_backup_path, client_backup_path):
  try:
    # you need to remove the previous file because it just appends the information every time you run the
    # backup function, i am using try/except because the first time the file doesnt exist.
    os.remove(client_backup_path + db_name + r'_sql.bak')
  except:
    print db_name + ' doesnt exist yet...'
  cur = conn_obj.cursor()
  try:
    # here i am using try/except because some system databases cant be backed up such as tempdb or 
    # a database might be problematic for any reason, perhaps an exclude mechanism is better, its
    # up to you.
    cur.execute('BACKUP DATABASE ? TO DISK=?', [db_name, server_backup_path + db_name + r'_sql.bak'])
    while cur.nextset(): 
      pass
    cur.close()
  except:
    print 'cant backup: ' + db_name

# take the list
dbs = list_databases(conn)

# take backup for each database
for db in dbs:
  backup_db(conn, db, server_backup_path, client_backup_path)

# close the connection
conn.close()

Now you have all your files at the mssql_backup directory and you can copy the file wherever you want. I hope this will be help other with the same problems out there. Ofcourse i am not a professional python developer, so i will be very glad to hear from you any improvements to my code 🙂

See you soon!