Search code examples
pythonsqlpyodbc

Cannot perform a backup or restore operation within a transaction

I am using PyODBC to back up my database, using following code:

SQL_command = """
                BACKUP DATABASE [MyDatabase]
                TO DISK = N'D:\MSSQL\BACKUP\MyDatabase_20141212.bak' WITH
                      NOFORMAT
                ,     NOINIT
                ,     NAME = N'MyDatabase_20141212'
                ,     SKIP
                ,     REWIND
                ,     NOUNLOAD
                ,     STATS = 10
              """

conn.cursor.execute(SQL_command)

conn.cursor.commit()

The above code give me an error message:

pyodbc.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)')

I tried to run the SQL code in SQL Server, and it works fine.

May I know what is wrong with my code?

Many thanks.

Solution

  • Your error says

    Cannot perform a backup or restore operation within a transaction.

    Transactions are started by default in pyodbc, so how do you execute a query without creating a transaction? Simply turn on autocommit:

    conn.autocommit = true
// do stuff
conn.autocommit = false

    The pyodbc FAQ has an entry about this.