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

user_11684977 0
Viewed 2701

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?

pyodbc python
1 Answers

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.

user_21443900
Related