Skip to content

making SQL Server backups using python and pyodbc

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.

5 Comments

  1. Ray wrote:

    Beat my head up against this issue for about an hour before I came across this (switching off of pymssql for the same reason you did – momentum is stalled). Thanks for documenting this bad boy.

    Monday, December 17, 2012 at 8:20 pm | Permalink
  2. Spot on with this write-up, I honestly believe that this website needs a lot more attention. I’ll probably be back again to read more, thanks for the information! jacqueline friedberg mugshot http://www2.smc.edu/photo/portfolio_galleries/2009galleries/jacqueline_friedberg/index.html

    Friday, April 12, 2013 at 2:18 am | Permalink
  3. navanjr wrote:

    I could kiss you.

    Tuesday, August 13, 2013 at 5:39 pm | Permalink
  4. Pranav wrote:

    Awesome it worked

    Monday, September 30, 2013 at 5:26 am | Permalink
  5. google.com wrote:

    This type of coat tends to thicken with temperature
    changes, or regular clipping, and it will also shed out minimally, but regularly, as well.

    , Charleston, SC: 15. I put a small amount of
    water in a plastic cup and quietly stood in front of my dog,
    without saying a word.

    Sunday, January 19, 2014 at 11:52 am | Permalink

One Trackback/Pingback

  1. MS SQL Backup with Python and pyodbc » mindless.gr on Wednesday, September 26, 2012 at 7:11 am

    [...] digging the internet for a solution, which came up quickly at “ryan’s tech blog” link on how to actually take the backup, after this i realised that there were multiple databases and i [...]