Python pyodbc BACKUP DATABASE TO DISK getting default MSSQL backup path added

I’m writing Python to backup MS SQL database to a different server. When I put the //remoteServer/share/filename into command, I get:

pyodbc.ProgrammingError: (‘42000’, “[42000] [Microsoft][ODBC SQL Server Driver]
[SQL Server]Cannot open backup device ‘E:\Data\MSSQL15.MSSQLSERVER\MSSQL\Backup\\REMOTESERVER\SHARE\foobar.bak’.
Operating system error 3(The system cannot find the path specified.). (3201) (SQLExecDirectW);
[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally. (3013)”)

Python 3.10.13, pyodbc 5.0.1 on Windows 10

SQLServer SQL Server Express 15.0.2104

Remote server Windows Server 2019. Share permissions include host name for SQL Server

When I remove //remoteserver/share/ from command leaving
backup_command = """BACKUP DATABASE BaseData TO DISK = 'foobar.bak';""" it runs,but the file ends up in default backup location(duh). Also, BACKUP DATABASE BaseData TO DISK = '\\REMOTESERVER\SHARE\foobar.bak' works when ran from SSMS while connected to SQLServer. How do I get it to stop adding default backup path?

import pyodbc

# Connection parameters
server="SQLServer"
database="BaseData"
username="User"
password = 'Password'

# Create a connection object
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
conn.autocommit = True
# Create a cursor object
cursor = conn.cursor()
# ---------------------------------------------------------------------------------------------------------------------------------
# Backup command 
backup_command = """BACKUP DATABASE BaseData TO DISK = '//REMOTESERVER/SHARE/foobar.bak';"""

# Execute the backup command
cursor.execute(backup_command)
#process return so that does not error
while (cursor.nextset()):
    pass

Same situation as this post except error different.

  • FWIW, this example uses backslashes in the UNC path. Maybe that makes a difference on the SQL Server side.

    – 




Thanks to @Gord Thompson I was inspired to make it:

backup_command = """BACKUP DATABASE BaseData TO DISK = '\\\\REMOTESERVER\\SHARE\\foobar.bak';"""

The \ has to be escaped with another \ for Python.
Although outside the scope of this question, for the permissions to work on REMOTESERVER, I had to add the SQL Server’s hostname to the shared folders Sharing and Security.

Leave a Comment