title: Getting "Execution Timeout Expired" Running SQL Server Backup through Invoke-Sqlcmd
body: <p><em>Disclaimer: I'm a newbie with Powershell.</em></p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is line 5 from the .ps1 script which has the Invoke-SqlCmd:</p>
  
  <pre><code>Invoke-Sqlcmd -ServerInstance "$serverInstance" -InputFile "C:\Program Files\DBBackups\dbfull_backups.sql" -Variable $sqlVariable -Verbose *&gt; $outputFile
  </code></pre>
  
  <p>Here is the BACKUP command from the SQL:</p>
  
  <pre><code>BACKUP DATABASE FrameStore TO DISK = @framestore_backup_file;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>BACKUP DATABASE successfully processed 467003 pages in 176.917 seconds (20.622 MB/sec).
  </code></pre>
  
  <p><strong>Version info:</strong><br>
  OS: Windows Server 2012 R2<br>
  MS SQL Server Express 2017<br>
  PSVersion 4.0</p>
  
  <p>(My apologies if this is already addressed in another SO page. I couldn't find this specific issue although it seems pretty basic.)</p>
  
  <p><strong>EDIT:</strong><br>
  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? </p>
  
  <p>According to <a href="https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps" rel="noreferrer">Microsoft Docs page for Invoke-Sqlcmd -QueryTimeout parameter</a>:</p>
  
  <blockquote>
    <p>Specifies the number of seconds before the queries time out. If a timeout value is not specified, the queries do not time out. </p>
  </blockquote>
  
  <p>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?).</p>