Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
966 views
in Technique[技术] by (71.8m points)

python - 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:MSSQLBACKUPMyDatabase_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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...