I have written a PowerShell script that restores a MS SQL 2008 Enterprise 
database from a backup. This backup was made using another PowerShell script.
The problem comes in with the actual restore command, it is started and 
exactly 10min after starting the command fails. There is nothing in the SQL 
logs to indicate what the problem could be. The error reported by PowerShell 
is (sensitive details removed):

Exception calling "SqlRestore" with "1" argument(s): "Restore failed for 
Server 'xxxxxxxxx'. "
At D:\transport_actions\powershell\AS.PS.Restore.Report.Server.ps1:92 char:23
+ $smoRestore.SqlRestore <<<< ($server)
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

The script is run on the server that is hosting the SQL 2008 instance and 
the location of the backup is local drive (internal or SAN drive) and the 
database is restored to a local drive. Smaller Databases will restore 
(completes in less than 10min) using the same script with the database name 
changed (for obvious reasons). Integrated security is used and the account 
does work, the ability to restore the smaller databases testify to that.
The odd things is, every time the script fails it is after 10min sometimes 
10min 4 sec. The backup has been tested by restoring using SSMS and there 
were no errors.
I have checked in SQL, there is a “Remote query timeout” setting under 
Connections on properties for the Server. This was 600 (10min) and change it 
to 1200 (20min) but no joy there. I have look at the session settings of 
PowerShell and found New-PSSessionOption and changing all the timeouts to 
20min did not have an possitive affect either.
Is there something obvious that I’m missing? Any help will be greatly 
appreciated! I can post the script if needed, can’t seem to find the option 
to “attach a file”

A very good colleague of mine found the answer to this one. Better Google 
skills than me :-)

There is a setting that can be set on the ConnectionContext of the server 
object of SMO called "StatementTimeout". Now even though a statement is not 
execute by PowerShell against SQL it would appear that this is translated as 
such and consequently changing this value higher (or ZERO if you prefer) 
resolved this issue. This value by default it would appear is 600 (seconds or 
10min).


"maartin" wrote:

> I have written a PowerShell script that restores a MS SQL 2008 Enterprise 
> database from a backup. This backup was made using another PowerShell script.
> The problem comes in with the actual restore command, it is started and 
> exactly 10min after starting the command fails. There is nothing in the SQL 
> logs to indicate what the problem could be. The error reported by PowerShell 
> is (sensitive details removed):
> 
> Exception calling "SqlRestore" with "1" argument(s): "Restore failed for 
> Server 'xxxxxxxxx'. "
> At D:\transport_actions\powershell\AS.PS.Restore.Report.Server.ps1:92 char:23
> + $smoRestore.SqlRestore <<<< ($server)
>     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
>     + FullyQualifiedErrorId : DotNetMethodException
> 
> The script is run on the server that is hosting the SQL 2008 instance and 
> the location of the backup is local drive (internal or SAN drive) and the 
> database is restored to a local drive. Smaller Databases will restore 
> (completes in less than 10min) using the same script with the database name 
> changed (for obvious reasons). Integrated security is used and the account 
> does work, the ability to restore the smaller databases testify to that.
> The odd things is, every time the script fails it is after 10min sometimes 
> 10min 4 sec. The backup has been tested by restoring using SSMS and there 
> were no errors.
> I have checked in SQL, there is a “Remote query timeout” setting under 
> Connections on properties for the Server. This was 600 (10min) and change it 
> to 1200 (20min) but no joy there. I have look at the session settings of 
> PowerShell and found New-PSSessionOption and changing all the timeouts to 
> 20min did not have an possitive affect either.
> Is there something obvious that I’m missing? Any help will be greatly 
> appreciated! I can post the script if needed, can’t seem to find the option 
> to “attach a file”