Pyodbc - SQL Server database restore incomplete

user_1773462 0
Viewed 940

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))
pyodbc restore sql-server
2 Answers

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()
user_72977000

0

After hours I found solution. It must be performed no MASTER, other sessions must be terminated, DB must be set to OFFLINE, then RESTORE and then set to ONLINE again.

def backup_and_restore():
server = 'localhost,1433'
database = 'myDB'
username = 'SA'
password = 'password'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE=MASTER;UID='+username+';PWD='+ password)
cnxn.autocommit = True

def execute(cmd):
    cursor = cnxn.cursor()
    cursor.execute(cmd)
    while cursor.nextset():
        pass
    cursor.close()

execute("BACKUP DATABASE [myDB] TO DISK = N'/usr/src/app/myDB.bak'")
execute("ALTER DATABASE [myDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;")
execute("ALTER DATABASE [myDB] SET OFFLINE;")
execute("RESTORE DATABASE [myDB] FROM DISK = N'/usr/src/app/myDB.bak' WITH REPLACE")
execute("ALTER DATABASE [myDB] SET ONLINE;")
execute("ALTER DATABASE [myDB] SET MULTI_USER;")
user_17222450
Related