Restore-SqlDatabase Wait operation timeout

Question

I have setup a process for Restoring Databases using the Restore-SqlDatabase cmdlet and scheduled this into a SQl Agent job. It's been working fine until I tried to restore a 160GB database. It basically fails after 600s with the error "The wait operation timed out". I've tried adding in a StatementTimeout of 0 to see if it would get round this and also change the Remote query Timeout on the SQL Server but it still bobmbs out after 10 minutes. Does anyone know if there is another setting I can change to increase the timeout? The Code I'm running is:

# Load Module path! its not loading by default in calls from powershell
$env:PSModulePath=$env:PSModulePath + ";" + "C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\"
## Load module and run functions now

#Import SQL Server PowerShell Provider.
Import-Module "sqlps" -DisableNameChecking

#GEt Around Wait Timeout Error with Remote Connection via PoSh
$server = "THESERVER"
$serverConn = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server
$serverConn.ConnectionContext.StatementTimeout = 0


#Restore Latest Copy of Test Database that was copied over
Restore-SqlDatabase -ServerInstance $server -Database "Test" -BackupFile "H:\SQLBACKUP\DATABASE_Refresh\Test_Latest.BAK" -ReplaceDatabase

Error That Appears is:

Restore-SqlDatabase : The wait operation timed out
At H:\SQLBACKUP\_AutoScripts\5_Test_DBRESTORE.ps1:16 char:1
+ Restore-SqlDatabase -ServerInstance $server -Database "Test ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Restore-SqlDatabase], Win32Exception
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand

Try to add: -ConnectionTimeout in the end of Restore-SQLDatabase and define the value (in minutes) — Vladimir Bundalo, Feb 07 '18 at 09:47
Hi Vladimir. Thanks for the reply. Unfortunately still get the same error after adding in the ConnectionTimeout 0 bit. — craig king, Feb 07 '18 at 12:00
Hi Sejal, I don't see an option for Execution Timeout for Restore-SqlDatabase cmdlet. — craig king, Feb 08 '18 at 10:21

score 0 · Answer 1 · answered Feb 07 '18 at 09:47

It looks like Restore-SqlDatabase has a -ConnectionTimeout parameter:

Specifies the number of seconds to wait for a server connection before a timeout failure. The timeout value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not timeout.

So your command can become:

Restore-SqlDatabase `
    -ServerInstance $server `
    -Database "Test" `
    -BackupFile "H:\SQLBACKUP\DATABASE_Refresh\Test_Latest.BAK" `
    -ReplaceDatabase `
    -ConnectionTimeout 0

Hi Vladimir. Thanks for the reply. Unfortunately still get the same error after adding in the ConnectionTimeout 0 bit. — craig king, Feb 07 '18 at 14:13

Restore-SqlDatabase Wait operation timeout

1 Answers1