我成功地使用Powershell和SMO来备份大多数数据库.但是,我有几个大型数据库,我收到“超时”错误“System.Data.sqlClient.sqlException：Timeout expired”.时间一直发生在10分钟.我已经尝试将ConnectionContext.StatementTimeout设置为0,6000,并设置为[System.Int32] :: MaxValue.设置没有任何区别.我发现了许多Google引用,表明将其设置为0会使其无限制.无论我尝试什么,超时都会持续发生在10分钟.我甚至将服务器上的远程查询超时设置为0(通过Studio Manager)无济于事.下面是我的SMO连接,其中我设置了超时和实际的备份功能.下面是我脚本的输出.
UPDATE
有趣的是,我使用VS 2008在C#中编写了备份函数,并且超时覆盖在该环境中工作.我正在将C#进程合并到我的Powershell脚本中,直到我找到为什么超时覆盖不能仅用于Powershell.这非常烦人！
function New-SMOconnection {
Param ($server,$ApplicationName= "PowerShell SMO",[int]$StatementTimeout = 0
)
# Write-Debug "Function: New-SMOconnection $server $connectionname $commandtimeout"
if (test-path variable:\conn) {
$conn.connectioncontext.disconnect()
} else {
$conn = New-Object('Microsoft.sqlServer.Management.Smo.Server') $server
}
$conn.connectioncontext.applicationName = $applicationName
$conn.ConnectionContext.StatementTimeout = $StatementTimeout
$conn.connectioncontext.Connect()
$conn
}
$smo = New-SMOConnection -server $server
if ($smo.connectioncontext.isopen -eq $false) {
Throw "Could not connect to server $($server)."
}
Function Backup-Database {
Param([string]$dbname)
$db = $smo.Databases.get_Item($dbname)
if (!$db) {"Database $dbname was not found"; Return}
$sqldir = $smo.Settings.BackupDirectory + "\$($smo.name -replace ("\\","$"))"
$s = ($server.Split('\'))[0]
$basedir = "\\$s\" + $($sqldir -replace (":","$"))
$dt = get-date -format yyyyMMdd-HHmmss
$dbbk = new-object ('Microsoft.sqlServer.Management.Smo.Backup')
$dbbk.Action = 'Database'
$dbbk.BackupSetDescription = "Full backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.MediaDescription = "Disk"
$target = "$basedir\$dbname\FULL"
if (-not(Test-Path $target)) { New-Item $target -ItemType directory | Out-Null}
$device = "$sqldir\$dbname\FULL\" + $($server -replace("\\","$")) + "_" + $dbname + "_FULL_" + $dt + ".bak"
$dbbk.Devices.AddDevice($device,'File')
$dbbk.Initialize = $True
$dbbk.Incremental = $false
$dbbk.LogTruncation = [Microsoft.sqlServer.Management.Smo.BackupTruncateLogType]::Truncate
If (!$copyonly) {
If ($kill) {$smo.KillAllProcesses($dbname)}
$dbbk.sqlBackupAsync($server)
}
$dbbk
}
Started sql backups for server LCFsqlxxx\sqlxxx at 05/06/2010 15:33:16 Statement TimeOut value set to 0. DatabaseName : OperationsManagerDW StartBackupTime : 5/6/2010 3:33:16 PM EndBackupTime : 5/6/2010 3:43:17 PM StartCopyTime : 1/1/0001 12:00:00 AM EndCopyTime : 1/1/0001 12:00:00 AM CopiedFiles : Status : Failed ErrorMessage : System.Data.sqlClient.sqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The backup or restore was aborted. 10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. at System.Data.sqlClient.sqlConnection.OnError(sqlException exception,Boolean breakConnection) at System.Data.sqlClient.sqlInternalConnection.OnError(sqlException exception,Boolean breakConnection) at System.Data.sqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.sqlClient.TdsParser.Run(RunBehavior runBehavior,sqlCommand cmdHandler,sqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler,TdsParserStateObject stateObj) at System.Data.sqlClient.sqlCommand.RunExecuteNonQueryTds(String methodName,Boolean async) at System.Data.sqlClient.sqlCommand.InternalExecuteNonQuery(DbAsyncResult result,String methodName,Boolean sendToPipe) at System.Data.sqlClient.sqlCommand.ExecuteNonQuery() at Microsoft.sqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand,ExecutionTypes executionType) Ended backups at 05/06/2010 15:43:23
