Restore-SqlDatabase times out with "The wait operation timed out"

[tn-5]

When restoring a large database using Restore-SqlDatabase the command times out after 600 seconds. This is regardless of the ConnectionTimeout parameter setting. Tried setting it to 0 and setting it to a large number, it always times out. When using Invoke-Sqlcmd to restore the same it works when setting QueryTimeout to 0

[StanDaMan0505]

Can confirm. The same happens to us. SqlServer Modul 22.2.0 times out after 10 minutes SqlServer Modul 21.1.18256 works fine same server and same database Might be an issue together with -TrustServerCertificate?

[recepdirek]

I have the same issue on my powershell restore script. Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Database $TargetDb -BackupFile $RestoreFile -RelocateFile @($RelocateData,$RelocateLog) -ReplaceDatabase -ConnectionTimeout 0 -TrustServerCertificate

updated database connection timeout to 0 and used that -ConnectionTimeout on the command but it didn't work. Got the error below:

powershell.exe : Restore-SqlDatabase : The wait operation timed out At line:1 char:1

• powershell.exe -File C:\db_refresh\db_refresh.ps1

•   + CategoryInfo          : NotSpecified: (Restore-SqlData...ation timed out:String) [], RemoteException
    + FullyQualifiedErrorId : NativeCommandError
  
  

At C:\db_refresh\db_refresh.ps1:45 char:1

• Restore-SqlDatabase -ServerInstance $TargetSqlServerInstance -Databas ...

•   + CategoryInfo          : InvalidOperation: (:) [Restore-SqlDatabase], Win32Exception
    + FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.RestoreSqlDatabaseCommand``
  

[tn-5]

Looks like separate ConnectionTimeout (to establish the connection) and QueryTimeout (to execute the actual SQL) paramters are needed for all commands, especially for those that are expected to be long running (such as restores)

[recepdirek]

I was using the Restore-SqlDatabase command, so it wasn't possible to set those separately. But I will try to use Invoke-Sqlcmd instead Restore-SqlDatabase.

[tn-5]

Yes, Invoke-Sqlcmd works since it supports the QueryTimeout parameter. Then you however lose the nice features of Restore-SqlDatabase like the progress indicator etc.