Pyodbc - SQL Server database restore incomplete

Question

Am trying to restore the database from python 3.7 in Windows using below script.

Drop database functions correctly as expected.

The restore database doesn't work as expected, database always shows "Restoring...." and never completes.

Database files are there in the specified path, but database is not usable.

How to fix this?

import pyodbc

try:
    pyconn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=MY-LAPTOP\\SQLEXPRESS;DATABASE=master;UID=sa;PWD=sa123')
    cursor = pyconn.cursor()
    pyconn.autocommit = True
    sql = "IF EXISTS (SELECT 0 FROM sys.databases WHERE name = 'data_test') BEGIN DROP DATABASE data_test END"
    pyconn.cursor().execute(sql)

    sql = """RESTORE DATABASE data_test FROM DISK='G:\\dbbak\\feb-20-2020\\data_test_backup_2020_02_20_210010_3644975.bak' WITH RECOVERY,
    MOVE N'Omnibus_Data' TO N'd:\\db\\data_test.mdf',
    MOVE N'Omnibus_Log' TO N'd:\\db\\data_test_1.ldf';"""
    print(sql)
    pyconn.cursor().execute(sql)
    while pyconn.cursor().nextset():
        pass 
    pyconn.cursor().close()
except Exception as e:
    print(str(e))

Answer 1

You're not using a single cursor, and so your program is exiting before the restore is complete, aborting it in the middle.

Should be something like:

conn = pyodbc.connect(' . . .')
conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql)
while cursor.nextset():
    pass 
cursor.close()