Backup MS SQL Database with pyodbc

Question

I am trying to backup my database with pyodbc.

The following SQL code has been tested and worked well directly in SQL Server Management Studio

DBCC SHRINKFILE(MyDB_v0_log, 100)
GO

BACKUP DATABASE comparables
TO  DISK = N'D:\MSSQL\BACKUP\MyDB_v0_noFSD.bak' WITH NOFORMAT
,   INIT
,   NAME = N'backup_MyDB_v0_noFSD.bak', SKIP, REWIND, NOUNLOAD
,   STATS = 10

And the code below is what I've tried in Python

conn = pyodbc.connect("driver={SQL Server};server=MyServer;database=MyDB;trusted_connection=true") 

cursor = conn.cursor()

SQL_command = """
                DBCC SHRINKFILE(comparables_v0_log, 100)

                BACKUP DATABASE MyDB
                TO  DISK = N'D:\MSSQL\BACKUP\MyDB_v0_noFSD.bak' WITH NOFORMAT
                ,   INIT
                ,   NAME = N'backup_MyDB_v0_noFSD.bak', SKIP, REWIND, NOUNLOAD
                ,   STATS = 10
              """

cursor.execute(SQL_command)
cursor.commit()

However, the above code generates an error:

pyodbc.Error: ('HY007', '[HY007] [Microsoft][ODBC SQL Server Driver]Associated statement is not prepared (0) (SQLNumResultCols)')

May I know what is wrong with my Python code?

You've basically got two commands there with no separator between them. Either send each command separately, or put a separator (GO or ;) between them.Also, if pyOdbc has a executeNonQuery method that would be what you need - the query will return no rows and the error seems to suggest it's expecting some. — simon at rcl
– simon at rcl, Commented Apr 24, 2015 at 13:21

Gord Thompson · Accepted Answer · 2015-04-25 10:57:30Z

I was able to recreate your specific error. According to the Python Database API Spec, database connections must always be opened with auto-commit disabled. In other words, database operations must always occur in a transaction by default.

However, the T-SQL DBCC SHRINKFILE and BACKUP operations cannot be performed in a transaction, so we need to tell pyodbc that we want auto-commit enabled, which we can do with

cnxn = pyodbc.connect(my_connection_string, autocommit=True);

When I made that change the error went away, but the backup was still not happening. I'm not sure why that is, because I can do the same thing from other ODBC connections (e.g., from VBScript) and it worked there.

One workaround would be to create a stored procedure in the SQL Server database, e.g.,

CREATE PROCEDURE dbo.doShrinkAndBackup
AS
BEGIN
    SET NOCOUNT ON;

    DBCC SHRINKFILE(myDb_log, 100);

    BACKUP DATABASE myDb
    TO  DISK = N'C:\__tmp\myDb.bak' WITH NOFORMAT
    ,   INIT
    ,   NAME = N'myDb backup', SKIP, REWIND, NOUNLOAD
    ,   STATS = 10;
END

and then invoke the stored procedure from Python

cnxn = pyodbc.connect(my_connection_string, autocommit=True);
crsr = cnxn.cursor()
crsr.execute('EXEC dbo.doShrinkAndBackup')

If you want to be able to tweak values at run-time then you can just add input parameters to the stored procedure.

Collectives™ on Stack Overflow

Backup MS SQL 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