How to backup a database by pyodbc

backup, database, pyodbc, python

Solution

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.

Problem

The backup statement can't be used in a transaction when it execute with pyodbc cursor. It seems that the pyodbc execute the query inside a default transaction. I have also tried to use the autocommit mode or add the commit statement before the backup statement. Both of these are not working. ``` #can't execute the backup statement in transaction cur.execute("backup database database_name to disk = 'backup_path'") #not working too cur.execute("commit;backup database database_name to disk = 'backup_path'") ``` Is it possible to execute the backup statement by pyodbc? Thanks in advance! -----Added aditional info----------------------------------------------------------------------- The backup operation is encapsulate in a function such as: ``` def backupdb(con, name, save_path): # with autocommit mode, should be pyodbc.connect(con, autocommit=True) con = pyodbc.connect(con) query = "backup database %s to disk = '%s'" % (name, save_path) cur = con.cursor() cur.execute(query) cur.commit() con.close() ``` If the function is called by following code, ``` backupdb('DRIVER={SQL Server};SERVER=.\sqlexpress;DATABASE=master;Trusted_Connection=yes', 'DatabaseName', 'd:\\DatabaseName.bak') ``` then the exception will be: ``` File "C:/Documents and Settings/Administrator/Desktop/bakdb.py", line 14, in <module>'d:\\DatabaseName.bak') File "C:/Documents and Settings/Administrator/Desktop/bakdb.py", line 7, in backupdb cur.execute(query) ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a backup or restore operation within a transaction. (3021) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally. (3013)') ``` With open the keyword autocommit=True, the function will run silently but there is no backup file generated in the backup folder.

Original source