CODEWITHSUNDEEP
databasepowershelltimeoutwaitsqlps
craig king
craig king

Reputation: 1

Restore-SqlDatabase Wait operation timeout

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

Upvotes: 0

Views: 1993

Answers (1)

gvee
gvee

Reputation: 17171

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

Upvotes: -1

Related Questions