I have a set of python scripts to help me manage a few SQL Servers at work, and one of the things I do is take database backups using BACKUP DATABASE and BACKUP LOG. I’ve been using pymssql to connect, but today tried switching to pyodbc. pymssql seems to be having momentum problems, so I figured I’d try the alternative and see how well it works.
I ran into two issues:
- pymssql supports “%s” as a parameter placeholders, and pyodbc wants “?”
- BACKUP and RESTORE operations on SQL Server don’t run like normal queries
The first was trivial to fix, the second took some digging. If you try to run a BACKUP via pyodbc, the cursor.execute() call starts and finishes with no error, but the backup doesn’t get made. With help from CubicWeb‘s post MS SQL Server Backuping gotcha, I learned that BACKUP and RESTOREs over ODBC trigger some kind of asynchronous / multiple result set mode. To get around this, you can poll for file size (as Cubicweb did), but that gets ugly when making a backup on a remote server.
In a backup, I think each “X percent processed.” message is considered a different result set to ODBC. Instead of polling the file size, you can call cursor.nextset in a loop to get all the “percent processed” sets:
After adding that while loop, backups of small and medium sized databases worked like a charm.
4 Comments