在 SQL Server 2008 上使用 sqlalchemy 等待数据库还原完成 [英] Waiting for DB restore to finish using sqlalchemy on SQL Server 2008

问题描述

我正在尝试在开发过程中自动化我的数据库恢复，在 SQL Server 2008 上使用 TSQL，使用 sqlalchemy 和 pyodbc 作为传输.

I'm trying to automate my db restores during development, using TSQL on SQL Server 2008, using sqlalchemy with pyodbc as a transport.

我正在执行的命令是:

"""创建数据库数据库名

"""CREATE DATABASE dbname

restore database dbname FROM DISK='C:\Backups\dbname.bak' WITH REPLACE,MOVE 'dbname_data' TO 'C:\Databases\dbname_data.mdf',MOVE 'dbname_log' TO 'C:\Databases\dbname_log.ldf'"""

restore database dbname FROM DISK='C:\Backups\dbname.bak' WITH REPLACE,MOVE 'dbname_data' TO 'C:\Databases\dbname_data.mdf',MOVE 'dbname_log' TO 'C:\Databases\dbname_log.ldf'"""

不幸的是，在 SQL Management Studio 中，代码运行后，我看到数据库仍处于正在恢复..."状态.

Unfortunately, the in SQL Management Studio, after the code has run, I see that the DB remains in state "Restoring...".

如果我通过管理工作室恢复，它可以工作.如果我使用子进程调用sqlcmd"，它会起作用.pymssql 在身份验证方面存在问题，甚至没有达到那个程度.

If I restore through management studio, it works. If I use subprocess to call "sqlcmd", it works. pymssql has problems with authentication and doesnt even get that far.

可能出了什么问题?

推荐答案

BACKUP 和 RESTORE 语句异步运行，因此它们不会在继续执行其余代码之前终止.

The BACKUP and RESTORE statements run asynchronously so they don't terminate before moving on to the rest of the code.

使用在 http://ryepup.unwashedmeme.com/blog/2010/08/26/making-sql-server-backups-using-python-and-pyodbc/ 为我解决了这个问题:

Using a while statement as described at http://ryepup.unwashedmeme.com/blog/2010/08/26/making-sql-server-backups-using-python-and-pyodbc/ solved this for me:

# setup your DB connection, cursor, etc
cur.execute('BACKUP DATABASE ? TO DISK=?', 
            ['test', r'd:\temp\test.bak'])
while cur.nextset():
    pass

这篇关于在 SQL Server 2008 上使用 sqlalchemy 等待数据库还原完成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！