Ryan’s Tech Blog

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.