This project is read-only.

invoke-Sqlbackup and timeout

Apr 19, 2010 at 3:28 PM

how can i change the timeout for making a backup?  This would have finished but the script quit too early.


PS D:\RMSoftware\DebugLogFiles> Invoke-SqlBackup
cmdlet Invoke-SqlBackup at command pipeline position 1Supply values for the following parameters:sqlserver: .\sql2005dbname: rm_rtd_denver_controlfilepath: d:\sqldata\2005\rm_rtd_denver_74177.bakInvoke-SqlBackup : Exception calling "SqlBackup" with "1" argument(s): "Backup failed for Server 'DHOLMES\sql2005'. "Microsoft.SqlServer.Management.Smo.FailedOperationException: Backup failed for Server 'DHOLMES\sql2005'.  ---> Microsoft.SqlServer.Management.Common.ExecutionailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Timeout expired.  The timeot period elapsed prior to completion of the operation or the server is not responding.The backup or restore was aborted.10 percent processed.20 percent processed.30 percent processed.40 percent processed.50 percent processed.60 percent processed.70 percent processed.80 percent processed.90 percent processed.

Apr 19, 2010 at 4:22 PM

It looks like by default the StatementTimeout for SMO operations is set at 600 seconds or 10 minutes. This can be overriden by changing the StatementTimeout property on the server object. Since Invoke-SqlBackup takes either an SMO server object or a string representing the server name you can do the following:

#Obtain a reference to you SQL server:

$server = Get-SqlServer "DHOLMES\sql2005"

#Change statementTimeout to 100 minutes:

$server.ConnectionContext.StatementTimeout = 6000

#Call Invoke-SqlBackup with server object:

Invoke-SqlBackup -sqlserver $server -dbname "rm_rtd_denver_control" -filepath "d:\sqldata\2005\rm_rtd_denver_74177.bak"

I haven't tested this to ensure the StatementTimeout property settting will address timeout as I think it should, so please let me know if this fixes the problem.

Apr 19, 2010 at 4:56 PM

I am also trying to put this in the background.

$instance =  Get-SqlServer "DHOLMES\sql2005"

$instance.ConnectionContext.StatementTimeout = 6000

Invoke-SqlBackup -sqlserver $sql2005 -dbname rm_rtd_denver_control -filepath d:\sqldata\2005\rm_rtd_denver_71477.bak

now seems to work.  how do i get this to run successfully in the background? I have tried the below but it finishes as soon as it starts.

PS D:\RMSoftware\DebugLogFiles> Start-Job -name backup -ScriptBlock {  Invoke-SqlBackup -sqlserver $sql2005 -dbname rm_rtd_denver_control -filepath d:\sqldata\2005\rm_rtd_denver_71477.bak } -InitializationScript { Import-Module adolib; Import-Module sqlserver; Import-Module sqlmaint}



Apr 19, 2010 at 6:06 PM

I see an issue with variable scope in the sqlserver parameter in your call to start-job. Here's a working example:

Start-Job -name backup -ScriptBlock {  Invoke-SqlBackup -sqlserver "$env:computername\sql2k8" -dbname pubs -filepath "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K\MSSQL\Backup\pubs.bak" } -InitializationScript { Import-Module sqlserver} 

If you need to pass variable from  the calling session you would need to use either param or $args in the scriptblock:

$serverName = "$env:computerName\sql2k8"

Start-Job -name backup -ScriptBlock {  Invoke-SqlBackup -sqlserver $args[0] -dbname pubs -filepath "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K\MSSQL\Backup\pubs.bak" } -InitializationScript { Import-Module sqlserver} -ArgumentList $serverName

If I try to pass a SMO server object as argument I receive the following error: "Method invocation failed because [Deserialized.Microsoft.SqlServer.Management.Smo.Server] doesn't contain a method named 'GetType'." This is due to the serialization PowerShell jobs do in the background. So if you want to work with a server object I would suggest creating the object in the start-job scriptblock. You can also define your server variable as global as a workaround. $global:server = .... and then you can reference the server variable in the scriptblock without passing it via -ArgumentList.

Once you start the job use get-job to see status and receive-job to view results including any error messages:

get-job | receive-job


Apr 22, 2010 at 3:05 PM

I don't think this works.

PS D:\sqldata\2005> Start-Job -Name restore_denver -ScriptBlock { Invoke-SqlRestore -sqlserver .\sql2005 -dbname RM_RTD_Denver_Control -filepath D:\sqldata\2005\RM_RTD_Denver_520_2010419_2012.bak -force } -InitializationScript {Import-Module adolib; Import-Module sqlserver }

was the command but seeing the job shows competing answers.  The looks like it is running but the Finished property is "ManualResetEvent".  Did you get this to work?

PS D:\sqldata\2005> Get-Job |fl

HasMoreData   : TrueStatusMessage :Location      : localhostCommand       :  Invoke-SqlRestore -sqlserver .\sql2005 -dbJobStateInfo  : RunningFinished      : System.Threading.ManualResetEventInstanceId    : f1c23c06-4eb0-4b0c-bdf3-9d1af0e1b3a6Id            : 1Name          : restore_denverChildJobs     : {Job2}Output        : {}Error         : {}Progress      : {}Verbose       : {}Debug         : {}Warning       : {}State         : Running

PS D:\sqldata\2005> Get-Job 2 |fl

StatusMessage :HasMoreData   : TrueLocation      : localhostRunspace      : System.Management.Automation.RemoteRunspaceCommand       :  Invoke-SqlRestore -sqlserver .\sql2005 -dbJobStateInfo  : RunningFinished      : System.Threading.ManualResetEventInstanceId    : 4107946f-c0fe-4eb6-90fd-9669e970a996Id            : 2Name          : Job2ChildJobs     : {}Output        : {}Error         : {}Progress      : {}Verbose       : {}Debug         : {}Warning       : {}State         : Running


(the pasting of crlf from the console doesn't seem to be working too well that is why the formatting is off)


Apr 22, 2010 at 4:19 PM

This example worked for me:

Start-Job -name backup -ScriptBlock {  Invoke-SqlBackup -sqlserver "$env:computername\sql2k8" -dbname pubs -filepath "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K\MSSQL\Backup\pubs.bak" } -InitializationScript { Import-Module sqlserver} 

Of course, the example doesn't implement the modified timeout setting. I wonder if that is what your hitting. Can you paste the output of this command:

get-job | receive-job | clip

Apr 22, 2010 at 4:25 PM

PS D:\sqldata\2005> Get-Job
Id              Name            State      HasMoreData     Location             Command

--              ----            -----      -----------     --------             -------

1               restore_denver  Running    True            localhost             Invoke-SqlRestore -sq...

3               restore_denver  Running    True            localhost             Invoke-SqlRestore -sq...

5               restore_denver  Running    True            localhost             Invoke-SqlRestore -sq...

PS D:\sqldata\2005> Get-Job 5 |Receive-Job

PS D:\sqldata\2005>


"Clip" wasn't recognized for me but the output was empty.

Apr 22, 2010 at 11:07 PM

Does it move from a running to complete state?

You could do something like this:

get-job| wait-job; get-job | receive-job

Because clip isn't present, I assume you're using XP?

Have you tried running a simpler job just to prove out whether jobs are working correctly without SQLPSX (I've heard about job problems on XP and with CTP V2 builds).  One of the examples from get-help start-job -examples would suffice.



Sep 18, 2010 at 3:59 AM

for the next release can the timeout be set to 0 in the invoke-sqlrestore function?


    switch ($sqlserver.GetType().Name)
        'String' { $server = Get-SqlServer $sqlserver }
        'Server' { $server = $sqlserver }
        default { throw 'Invoke-SqlRestore:Param sqlserver must be a String or Server object.' }
	#some restores might take longer than the timeout.  set the timeout to infinite
	$previousTimeout = $server.ConnectionContext.StatementTimeout 
	$server.ConnectionContext.StatementTimeout = 0


and then return the original value at the end?

    try { $restore.SqlRestore($server) }
    catch {
            $ex = $_.Exception
            $message = $ex.message
            $ex = $ex.InnerException
            while ($ex.InnerException)
                $message += "`n$ex.InnerException.message"
                $ex = $ex.InnerException
            Write-Error $message
	finally {
		$server.ConnectionContext.StatementTimeout = $previousTimeout
i can't think of any harm and it sure would help this situation a lot.



Sep 18, 2010 at 4:01 AM
dholmes wrote:

for the next release can the timeout be set to 0 in the invoke-sqlrestore function?



and in invoke-sqlbackup too

Sep 21, 2010 at 11:48 AM

@dholmes thanks for the feedback -- I agree setting the timeout to 0 makes a lot of sense especially for restores. I'll get this into the next release.

Sep 21, 2010 at 11:48 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Nov 6, 2010 at 9:27 PM

This has been fixed in the 2.3 release