Cannot perform a backup or restore operation within a transaction

Question

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.

Hypotension

Answer 1

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.

Hardshell

This works for pymssql as well – Nadia

Answer 2

By default all pyodbc connections start with a transaction.

You need to turn autocommit on - using the autocommit keyword to the connect function:

conn.autocommit = true

Leniency

This works for pymssql as well – Nadia

Answer 3

The other answers are correct. You do need to set autocommit. However, the transaction will complete but the backup won't actually be taken because of a quirk in SQL Server and the way it returns status messages for backup and restore operations.

To work around this you need to loop through these return messages until none remain:

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)
while conn.cursor.nextset():
     pass 

conn.cursor.close()

Hyperphagia

Answer 4

With me it works fine like this:

while True:
    time.sleep(1)
    result = cursor.execute(
                 """SELECT command FROM sys.dm_exec_requests 
                 WHERE command LIKE 'BACKUP%'").fetchone()
    if not result:
        break

Pilfer