Python script to create a backup file of a SQL Server database (with pyodbc)

Question

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.

The SQL script itself seems to work fine when run in SQL Server:

BACKUP DATABASE TEST_DB
TO DISK = 'D:/test/test_db.BAK';

However, when I try to run it from a python script it fails:

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()

First of all, if I don't add the line "con.autocommit = True", it will fail with the message:

Cannot perform a backup or restore operation within a transaction. (3021)

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.

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?

That fixes the problem. Thanks. I don't understand why this issue does not happen when the query is run directly on SSMS though. — Pitrako Junior
– Pitrako Junior, Commented May 31, 2020 at 19:03

Gord Thompson · Accepted Answer · 2020-05-31 20:16:45Z

2

The solution, as described in this GitHub issue, is to call .nextset() repeatedly after executing the BACKUP statement ...

crsr.execute(backup_statement)
while (crsr.nextset()):
    pass

... to "consume" the progress messages issued by the BACKUP. If those messages are not consumed before the connection is closed then SQL Server figures that something went wrong and cancels the backup.

SSMS can apparently capture those messages directly, but an ODBC connection must issue calls to the ODBC SQLMoreResults function to retrieve each message, and that's what happens when we call the pyodbc .nextset() method.

answered May 31, 2020 at 20:16

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python script to create a backup file of a SQL Server database (with pyodbc)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related