问题描述
我正在尝试通过以下语句使用"Backup-SQLDatabase" cmdlet备份大型数据库，但是10分钟后出现超时错误.
I’m trying to take back-up of a large database using "Backup-SQLDatabase" cmdlet using following statement, but I’m getting time-out error after 10 minutes.
{Backup-SqlDatabase -ServerInstance $Server -Database $DatabaseName -BackupFile $BackUpFile -CompressionOption On -ConnectionTimeout 0 -Initialize -Verbose -ea Stop}
执行完600秒后，就出现了错误:
Here’s the error exactly after 600 seconds of execution :
详细:已处理60％.详细信息:备份或还原已中止.等待操作超时 + CategoryInfo:InvalidOperation:(:) [Backup-SqlDatabase]，Win3 2异常 + FullyQualifiedErrorId:ExecutionFailed，Microsoft.SqlServer.Management.P owerShell.BackupSqlDatabaseCommand + PSComputerName:本地主机
VERBOSE: 60 percent processed.VERBOSE: The backup or restore was aborted.The wait operation timed out + CategoryInfo : InvalidOperation: (:) [Backup-SqlDatabase], Win3 2Exception + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.P owerShell.BackupSqlDatabaseCommand + PSComputerName : localhost
我抬头看了一下互联网，发现一个此处.但是，该问题在SQL Server 2012(11.0.339)中仍然存在.
I looked up at internet and found a bug filled here.However, the issue still exists in SQL Server 2012 (11.0.339).
我还尝试按照给定的此处将远程查询超时"重新配置为0 ，但问题仍然存在.
I’ve also tried reconfiguring "remote query timeout" to 0 as given here, but the issue persists.
这实际上是一个很奇怪的问题. PowerShell是用于自动化的，脚本要花费超过10分钟的时间才能运行. "Backup-SQLDatabase"应该考虑到这一点.
This is actually very weird issue. PowerShell is for automation and scripts do take more than 10 minutes to run. "Backup-SQLDatabase" should have considered this.
请提出一种解决方法，我可以在使用此cmdlet时解决此问题.否则，我必须使用SMO类或基本的T-SQL重新编写代码.
Please suggest a workaround by which I can fix this while using this cmdlet.Else , I’ve to re-write the code using SMO classes or basic T-SQL.
推荐答案
我对此进行了一些研究，并得出以下解决方法:
I did some research on this and came around the following workaround :
$serverConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server$serverConn.ConnectionContext.StatementTimeout = 0
$serverConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server$serverConn.ConnectionContext.StatementTimeout = 0
Backup-SqlDatabase -InputObject $serverConn -Database abc -BackupFile "L:\123\abc.bak"
当我们将服务器名称作为字符串传递时，它将尝试创建自己的连接，而我们没有选择将QueryTimeout从600更改为0.
When we pass Server name as a string, it tries to create it's own connection and we don't have the option to change QueryTimeout from 600 to 0.
但是，我们可以创建一个SMO.Server对象，并在设置所需的属性后使用它.
However, we can create a SMO.Server object and use it after setting desired properties.
希望有帮助！
这篇关于Backup-SQLDatabase cmdlet中的超时问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！