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}
Here’s the error exactly after 600 seconds of execution :
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
I looked up at internet and found a bug filled here. However, the issue still exists in SQL Server 2012 (11.0.339).
I’ve also tried reconfiguring “remote query timeout” to 0 as given here, but the issue persists.
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.
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
Backup-SqlDatabase -InputObject $serverConn -Database abc -BackupFile "L:\123\abc.bak"
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.
However, we can create a SMO.Server object and use it after setting desired properties.
Hope it helps!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With