search

microsoft / SQLServerPSModule

This repo is the home of SQL Server PowerShell Module development.
MIT License
45 stars 1 forks source link

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

Open tn-5 opened 10 months ago

tn-5 commented 10 months ago

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 commented 4 months ago

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 commented 2 months ago

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

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

tn-5 commented 2 months ago

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 commented 2 months ago

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 commented 2 months ago

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.