How to backup a database by pyodbc

Assuming you are using SQL Server, specify autocommit=True when the connection is built:

>>> import pyodbc
>>> connection = pyodbc.connect(driver='{SQL Server Native Client 11.0}', 
                                server='InstanceName', database='master', 
                                trusted_connection='yes', autocommit=True)
>>> backup = "BACKUP DATABASE [AdventureWorks] TO DISK = N'AdventureWorks.bak'"
>>> cursor = connection.cursor().execute(backup)
>>> connection.close()

This is using pyodbc 3.0.7 with Python 3.3.2. I believe with older versions of pyodbc you needed to use Cursor.nextset() for the backup file to be created. For example:

>>> import pyodbc
>>> connection = pyodbc.connect(driver='{SQL Server Native Client 11.0}', 
                                server='InstanceName', database='master', 
                                trusted_connection='yes', autocommit=True)
>>> backup = "E:\AdventureWorks.bak"
>>> sql = "BACKUP DATABASE [AdventureWorks] TO DISK = N'{0}'".format(backup)
>>> cursor = connection.cursor().execute(sql)
>>> while cursor.nextset():
>>>    pass
>>> connection.close()

It's worth noting that I didn't have to use Cursor.nextset() for the backup file to be created with the current version of pyodbc and SQL Server 2008 R2.

After hours I found solution. It must be performed no MASTER, other sessions must be terminated, DB must be set to OFFLINE, then RESTORE and then set to ONLINE again.

def backup_and_restore():
server = 'localhost,1433'
database = 'myDB'
username = 'SA'
password = 'password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE=MASTER;UID='+username+';PWD='+ password)
cnxn.autocommit = True

def execute(cmd):
    cursor = cnxn.cursor()
    cursor.execute(cmd)
    while cursor.nextset():
        pass
    cursor.close()

execute("BACKUP DATABASE [myDB] TO DISK = N'/usr/src/app/myDB.bak'")
# do something .......
execute("ALTER DATABASE [myDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;")
execute("ALTER DATABASE [myDB] SET OFFLINE;")
execute("RESTORE DATABASE [myDB] FROM DISK = N'/usr/src/app/myDB.bak' WITH REPLACE")
execute("ALTER DATABASE [myDB] SET ONLINE;")
execute("ALTER DATABASE [myDB] SET MULTI_USER;")