4

I am attempting to use python to automate some of our database restores. When I run the SQL query the database restores and comes back up like normal, but when I run the python script that uses the same SQL query the database is left in the restoring mode.

Any help with what I'm doing wrong would be greatly appreciated.

import pyodbc

#Set these Variables
sourceserver = 'MySourceServerName'
destinationserver = 'MyDestinationServerName'
dbname = 'test'


sourceserverplusport = sourceserver + ',1433'
destinationserverplusport = destinationserver + ',1433'


def ExecSQL(serverplusport, dbnameIN, sql):
    try:
        connexecsql = pyodbc.connect('Driver={SQL Server};'
                                         'Server=' + serverplusport +';'
                                         'Database=' + dbnameIN + ';'
                                         'Trusted_Connection=yes;')

        #Set AutoCommit
        if connexecsql is not None:
           connexecsql.autocommit = True
       
        print(sql)
        cursor = connexecsql.cursor()
        cursor.execute(sql)
    finally:    
        cursor.commit
        cursor.close
        connexecsql.commit
        connexecsql.close


sqlcommand = r"""IF @@SERVERNAME = 'MyDestinationServerName'
RESTORE DATABASE test FROM DISK = '\\MyBackupPath\test.BAK' WITH FILE = 1, MOVE N'test' to N'X:\Database\MDF\test.mdf',  MOVE N'test_log' to N'Y:\Database\LDF\test_log.ldf',  NOUNLOAD,  REPLACE, RECOVERY, STATS = 5;"""

ExecSQL(destinationserverplusport, "master", sqlcommand)

If I add one more ExecSQL command at the end of the script to run RESTORE DATABASE test WITH RECOVERY; at the end returns the error:

The database cannot be recovered because the log was not restored."

Including the REPLACE with the RECOVERY removes the error but leaves the database in restoring still.

0

2 Answers 2

5

Pyodbc won't progress past info messages automatically, and RESTORE generates a lot of them. So you must process them with cursor.nextset() or else you're actually aborting the RESTORE by running cursor.close() before it actually completes.

Eg

cursor.execute(sql)
while cursor.nextset():
    pass 
cursor.close()
-1

Have you considered using https://dbatools.io/ instead?

It's FREE and highly regarded.

Check out https://docs.dbatools.io/#Restore-DbaDatabase.

No disrespect to Python intended.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.