title: Python script to create a backup file of a SQL Server database (with pyodbc)
body: <p>I am trying to write a python script to do a backup of a SQL Server database and then restore it into a new database.</p>
  
  <p>The SQL script itself seems to work fine when run in SQL Server:</p>
  
  <pre><code>BACKUP DATABASE TEST_DB
  TO DISK = 'D:/test/test_db.BAK';
  </code></pre>
  
  <p>However, when I try to run it from a python script it fails:</p>
  
  <pre><code>con = pyodbc.connect('UID=xx; PWD=xxxxxx, driver='{SQL Server}', server=r'xxxxxx', database='TEST_DB')
  sql_cursor = con.cursor()
  query = ("""BACKUP DATABASE TEST_DB
              TO DISK = 'D:/test/test_db.BAK';""")
  con.autocommit = True
  sql_cursor.execute(query1)
  con.commit()
  </code></pre>
  
  <p>First of all, if I don't add the line "con.autocommit = True", it will fail with the message: </p>
  
  <pre><code>Cannot perform a backup or restore operation within a transaction. (3021)
  </code></pre>
  
  <p>No idea what a transaction is. I read in another post that the line "con.autocommit = True" removes the error, and indeed it does. I have no clue why though.</p>
  
  <p>Finally, when I run the python script with con.autocommit set to True, no errors are thrown, the BAK file can be seen temporarily in the expected location ('D:/test/test_db.BAK'), but when the script finishes running, the BAK file disappears (????). Does anyone know why this is happening?</p>