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.

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

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!