title: How to backup a database by pyodbc
body: <p>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.</p>
  
  <pre><code>#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'")
  </code></pre>
  
  <p>Is it possible to execute the backup statement by pyodbc? Thanks in advance!</p>
  
  <p>-----Added aditional info-----------------------------------------------------------------------</p>
  
  <p>The backup operation is encapsulate in a function such as:</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>If the function is called by following code, </p>
  
  <pre><code>backupdb('DRIVER={SQL Server};SERVER=.\sqlexpress;DATABASE=master;Trusted_Connection=yes',
               'DatabaseName',
               'd:\\DatabaseName.bak')
  </code></pre>
  
  <p>then the exception will be:</p>
  
  <pre><code>File "C:/Documents and Settings/Administrator/Desktop/bakdb.py", line 14, in &lt;module&gt;'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)')
  </code></pre>
  
  <p>With open the keyword autocommit=True, the function will run silently but there is no backup file generated in the backup folder. </p>