Timeout Error in Backup-SQLDatabase Cmdlet

Question

I am trying to back up a large database using the Backup-SQLDatabase cmdlet using the following statement, but Im getting a timeout error after 10 minutes.

{Backup-SqlDatabase -ServerInstance $Server -Database $DatabaseName -BackupFile $BackUpFile -CompressionOption On -ConnectionTimeout 0 -Initialize -Verbose -ea Stop}

Gets an error after exactly 600 seconds of execution:

VERBOSE: 60 percent processed. VERBOSE: The backup or restore was interrupted. Expected Operation Pending + CategoryInfo: InvalidOperation: (:) [Backup-SqlDatabase], Win3 2Exception + FullyQualifiedErrorId: ExecutionFailed, Microsoft.SqlServer.Management.P owerShell.BackupSqlDatabaseCommand + PSComputerName: localhost

I looked on the Internet and found an error filled here . However, the problem still exists in SQL Server 2012 (11.0.339).

Ive also tried to reconfigure the "remote request timeout" to 0, as indicated here , but the problem persists.

This is indeed a very strange problem. PowerShell is designed to be automated and takes more than 10 minutes to execute scripts. "Backup-SQLDatabase" should have considered this.

Please suggest a workaround with which I can fix this when using this cmdlet. Else, Ive to rewrite code using SMO classes or basic T-SQL.

Answer 1

I did some research on this and came to 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" 

, , QueryTimeout 600 0.

SMO.Server .

, !