Errors \ How-to - PYODBC to create SQL Express Backup

[BenFricke]

Hello

I am trying to make a SQL Express .BAK file of an SDE using the PYODBC in pywin32.

I have the sample code (below), but am not too sure if I have it set-up correctly.  Could anyone explain the parameters in an example?  Specifically the connection parameters.

When I run the script that I have so far, I am getting an error about SQL.    Any help will be appreciated.

Ben

Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53)')

Sample Code that I used from another post:

1. import pyodbc 
2.  
3. conn = pyodbc.connect('DRIVER={SQL Server};SERVER= <server name>;UID=sa;PWD=sa;Trusted_Connection=yes', autocommit=True) 
4. backup_path = 'C:\\temp\\' 
5. db = "VECTOR" 
6.  
7. def backup_db(conn, db, backup_path): 
8.   cur = conn.cursor() 
9.   try: 
10.     cur.execute('BACKUP DATABASE ? TO DISK=?', [db, backup_path + db + r'.bak']) 
11.     while cur.nextset():  
12.       pass 
13.     cur.close() 
14.   except: 
15.     print 'Unable to backup: ' + db 
16.  
17. backup_db(conn, db, backup_path) 
18.  
19. conn.close()

[JoshuaBixby]

Is the sample code above what you used to create your code or is it your code?  If the former, please post your code.  If the latter, start by replacing <server name> with an actual SQL Server instance name.  Also, is your password for the sa user really 'sa'? 

[BenFricke]

Hello. This is what I have below with the PW altered. I was unsure about how to enter the server name. I tried mcgis_sqlmcgis, sqlmcgis, mcgis\sqlmcgis, mcgis
sqlmcgis. Thanks

import pyodbc

conn = pyodbc.connect('DRIVER={SQL Server};SERVER= mcgis\sqlmcgis ;UID=sa;PWD=trailertrash;Trusted_Connection=yes', autocommit=True)

backup_path = 'C:
Backups-GIS-ToExternal
'

db = "RWSA_GIS"

def backup_db(conn, db, backup_path):

cur = conn.cursor()

try:

cur.execute('BACKUP DATABASE ? TO DISK=?', )

while cur.nextset():

pass

cur.close()

except:

print 'Unable to backup: ' + db

backup_db(conn, db, backup_path)

conn.close()

[JoshuaBixby]

OK, a few comments:

• Trusted_Connection loosely means "Windows Authentication."  It appears you want to use SQL Server/database authentication and not operating system authentication.  Your connection string has both, and I don't know which one takes priority over the other, so I suggest you drop Trusted_Connection if you want to use database authentication.
• It could be your "Driver=SQL Server" is causing the issue.  As the Connecting to SQL Server from Windows pyodbc covers, there "many SQL Server ODBC drivers written and distributed by Microsoft."  The {SQL Server} driver is the oldest and isn't always installed on machines, or doesn't always work well with newer versions of SQL Server when installed.  I would recommend updating the connection string to use a newer driver that is likely already installed.
• You are using backslashes with your SQL Server name, which is common for SQL Server named instances, but in Python backslashes are escape characters for strings.  You may need to escape your backslash or use triple quotes for your connection string.

[BenFricke]

Thank you very much. Your suggestion about the SQL driver seemed to be the key to this problem.

Cheers

Ben