Getting "Execution Timeout Expired" Running SQL Server Backup through Invoke-Sqlcmd

Question

Disclaimer: I'm a newbie with Powershell.

I am trying to script the backup of two SQL Server Express databases using a SQL script run from Powershell. When I run the SQL From SSMS, it runs fine with no timeout. When I run the SQL from a Powershell script the small database backup succeeds, but larger db backup fails with this error:

Invoke-Sqlcmd : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
At C:\Program Files\DBBackups\dbfull_backup.ps1:5 char:1
+ Invoke-Sqlcmd -ServerInstance "$serverInstance" -InputFile "C:\Program Files\ ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Here is line 5 from the .ps1 script which has the Invoke-SqlCmd:

Invoke-Sqlcmd -ServerInstance "$serverInstance" -InputFile "C:\Program Files\DBBackups\dbfull_backups.sql" -Variable $sqlVariable -Verbose *> $outputFile

Here is the BACKUP command from the SQL:

BACKUP DATABASE FrameStore TO DISK = @framestore_backup_file;

Is the timeout different when run through Powershell/Invoke-Sqlcmd than from SSMS? When run in SSMS, the backup takes under 3 minutes so not close to the 10 minute number I saw in the docs.microsoft documentation. Here is the backup output when run in SSMS:

BACKUP DATABASE successfully processed 467003 pages in 176.917 seconds (20.622 MB/sec).

Version info:
OS: Windows Server 2012 R2
MS SQL Server Express 2017
PSVersion 4.0

(My apologies if this is already addressed in another SO page. I couldn't find this specific issue although it seems pretty basic.)

EDIT:
I went ahead and tried adding the -QueryTimeout parameter set to 3600 seconds. This allowed the backup to succeed without the error. So, what is the default?

According to Microsoft Docs page for Invoke-Sqlcmd -QueryTimeout parameter:

Specifies the number of seconds before the queries time out. If a timeout value is not specified, the queries do not time out.

So... if that is true and I didn't specify the timeout, why is it timing out? Did this change from PS version 4 to the version documented? Does it seem like bad practice to set a timeout on a backup? I'm interested in opinions on this. It works now, but I'm still not confident in my solution (work-around?).

Answer 1

Long question short answer you need to use parameter -Querytimeout X where X is time in seconds

query in question will work like this where 0 make infinite timeout we can use an integer value between 1 and 65535.

Invoke-Sqlcmd -ServerInstance "$serverInstance" -Querytimeout 0 -InputFile "C:\Program Files\DBBackups\dbfull_backups.sql" -Variable $sqlVariable -Verbose *> $outputFile

Answer 2

So you have hit the famous 30 seconds timeout of the SMO object.

Grant Fritchey wrote about it back 2010. Read about it here: https://www.scarydba.com/2010/04/16/powershell-smo-problem/

If you set it = 0 - You will have told it to run infinite.

A more recent blog post about the same: https://fbrazeal.wordpress.com/2015/06/29/sqlps-tips/

Edited:

Depending on what version you're running, you should read this also: https://blogs.technet.microsoft.com/heyscriptingguy/2013/05/06/10-tips-for-the-sql-server-powershell-scripter/