如何通过pyodbc备份数据库 [英] How to backup a database by pyodbc
问题描述
使用pyodbc游标执行时,备份语句不能在事务中使用.似乎pyodbc在默认事务内执行查询. 我也尝试过使用自动提交模式或在备份语句之前添加提交语句.这两个都不起作用.
The backup statement can't be used in a transaction when it execute with pyodbc cursor. It seems that the pyodbc execute the query inside a default transaction. I have also tried to use the autocommit mode or add the commit statement before the backup statement. Both of these are not working.
#can't execute the backup statement in transaction
cur.execute("backup database database_name to disk = 'backup_path'")
#not working too
cur.execute("commit;backup database database_name to disk = 'backup_path'")
是否可以通过pyodbc执行备份语句?预先感谢!
Is it possible to execute the backup statement by pyodbc? Thanks in advance!
-----添加了附加信息--------------------------------------- --------------------------------
-----Added aditional info-----------------------------------------------------------------------
备份操作封装在以下功能中:
The backup operation is encapsulate in a function such as:
def backupdb(con, name, save_path):
# with autocommit mode, should be pyodbc.connect(con, autocommit=True)
con = pyodbc.connect(con)
query = "backup database %s to disk = '%s'" % (name, save_path)
cur = con.cursor()
cur.execute(query)
cur.commit()
con.close()
如果通过以下代码调用该函数,
If the function is called by following code,
backupdb('DRIVER={SQL Server};SERVER=.\sqlexpress;DATABASE=master;Trusted_Connection=yes',
'DatabaseName',
'd:\\DatabaseName.bak')
那么例外将是:
File "C:/Documents and Settings/Administrator/Desktop/bakdb.py", line 14, in <module>'d:\\DatabaseName.bak')
File "C:/Documents and Settings/Administrator/Desktop/bakdb.py", line 7, in backupdb cur.execute(query)
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a backup or restore operation within a transaction. (3021) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally. (3013)')
在打开关键字autocommit = True的情况下,该功能将以静默方式运行,但是备份文件夹中没有生成备份文件.
With open the keyword autocommit=True, the function will run silently but there is no backup file generated in the backup folder.
推荐答案
假定您正在使用SQL Server,则在建立连接时指定autocommit=True
:
Assuming you are using SQL Server, specify autocommit=True
when the connection is built:
>>> import pyodbc
>>> connection = pyodbc.connect(driver='{SQL Server Native Client 11.0}',
server='InstanceName', database='master',
trusted_connection='yes', autocommit=True)
>>> backup = "BACKUP DATABASE [AdventureWorks] TO DISK = N'AdventureWorks.bak'"
>>> cursor = connection.cursor().execute(backup)
>>> connection.close()
这是将pyodbc 3.0.7
与Python 3.3.2
一起使用.我相信在旧版本的Pyodbc中,您需要使用 Cursor.nextset()用于创建备份文件.例如:
This is using pyodbc 3.0.7
with Python 3.3.2
. I believe with older versions of pyodbc you needed to use Cursor.nextset() for the backup file to be created. For example:
>>> import pyodbc
>>> connection = pyodbc.connect(driver='{SQL Server Native Client 11.0}',
server='InstanceName', database='master',
trusted_connection='yes', autocommit=True)
>>> backup = "E:\AdventureWorks.bak"
>>> sql = "BACKUP DATABASE [AdventureWorks] TO DISK = N'{0}'".format(backup)
>>> cursor = connection.cursor().execute(sql)
>>> while cursor.nextset():
>>> pass
>>> connection.close()
值得注意的是,对于使用当前版本的pyodbc和SQL Server 2008 R2创建的备份文件,我不必使用Cursor.nextset().
It's worth noting that I didn't have to use Cursor.nextset() for the backup file to be created with the current version of pyodbc and SQL Server 2008 R2.
这篇关于如何通过pyodbc备份数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!