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.