Runspace with foreach loop

I have a question on the runspace, I am also trying to implement this, but using a foreach loop and with a runspace, can this be done? I have a foreach loop for each database, then i am doing step 3 in the foreach loop (3. Create runspace and start it) like so:

ForEach ($DB in $Databases)
{
# 3 Create runspace and start it
$runspace = [PowerShell]::Create()
[void]$runspace.AddScript($scriptblock)
[void]$runspace.AddArgument($DB.name)
[void]$runspace.AddArgument($BackupType.ToString())
[void]$runspace.AddArgument($BackupPath.ToString())
$runspace.RunspacePool = $pool
$runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }

}

this does work, However the problem or error I am getting is
Exception calling “EndInvoke” with “1” argument(s): "The WriteObject and
WriteError methods cannot be called from outside the overrides of the
BeginProcessing, ProcessRecord, and EndProcessing methods, and they can only
be called from within the same thread. Validate that the cmdlet makes these
calls correctly, or contact Microsoft Customer Support Services.

which when i see the backup is happening for each database, it seems to overwrite one over the other, and not backing all of them up parallel… do you by chance know what i may be doing wrong?

thanks in advance

You will have to post the code where you dispose the runspace, I feel like the problem is there…

Here is the dispose code part, sorry about that and thank you, below is the code:

6 Wait for runspaces to complete

while ($runspaces.Status.IsCompleted -notcontains $true) {}

7.1 Cleanup runspaces

foreach ($runspace in $runspaces) {
[void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
$runspace.Pipe.Dispose()
}

7.2 Cleanup runspace pool

$pool.Close()
$pool.Dispose()

Cleanup SQL Connections

Sorry yes, here is the dispose code part below:

6 Wait for runspaces to complete

while ($runspaces.Status.IsCompleted -notcontains $true) {}

7.1 Cleanup runspaces

foreach ($runspace in $runspaces) {
[void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
$runspace.Pipe.Dispose()
}

7.2 Cleanup runspace pool

$pool.Close()
$pool.Dispose()

Cleanup SQL Connections

Sorry i realized, probably showing everything is more beneficial than cuts and pieces of it, so here is the entire Code below:

# 2 Create reusable scriptblock
$scriptblock = {
Param(
[string] $DBName
,[string] $BackupType
,[string] $BackupPath
)

Process
{
$Date = Get-Date -Format “yyyy_MM_dd_HH_mm”
$StringDate = $Date.ToString()

If($BackupType -eq ‘Trn’)
{
$SQLStatement = “BACKUP LOG [” + $DBName + “] TO DISK = N’” + $BackupPath + ""+ $DBName + “" + $StringDate + “.trn’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}
ElseIf($BackupType -eq ‘Diff’)
{
$SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N’” + $BackupPath + ""+ $DBName + "
” + $StringDate + “.Diff’ WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}
Else
{
$SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N’” + $BackupPath + ""+ $DBName + “_” + $StringDate + “.bak’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}

Invoke-Sqlcmd -Query $SQLStatement -ServerInstance “Server001”
}

}

# 4 Add to Runspace Pool
# 5 Add runspace to runspace collection
ForEach ($DB in $Databases)
{
#$script = “scriptblock -DBName “” + $DB.name + “” -BackupType “” + $BackupType + “” -BackuPath “” + $BackupPath + “””

# 3 Create runspace and start it
$runspace = [PowerShell]::Create()#.AddScript($scriptblock).AddArgument($DB.name).AddArgument($BackupType.ToString()).AddArgument($BackupPath.ToString())
#$runspace = [PowerShell]::Create()
[void]$runspace.AddScript($scriptblock)
[void]$runspace.AddArgument($DB.name)
[void]$runspace.AddArgument($BackupType.ToString())
[void]$runspace.AddArgument($BackupPath.ToString())
$runspace.RunspacePool = $pool
$runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }

#BackupProcess -DBName $DB.name.ToString() -BackupType $BackupType.ToString() -BackupPath $BackupPath.ToString()
#$DB.name

#Write-Host $(“{0} is currently the primary replica for the {1} database. Backing Up.” -f [System.Environment]::MachineName, $DB.name)

# TODO: Backup and Upload To S3
#Write-Host “Execution completed”
}

# 6 Wait for runspaces to complete
while ($runspaces.Status.IsCompleted -notcontains $true) {}

# 7.1 Cleanup runspaces
foreach ($runspace in $runspaces) {
[void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
$runspace.Pipe.Dispose()
}

# 7.2 Cleanup runspace pool
$pool.Close()
$pool.Dispose()

# Cleanup SQL Connections
[System.Data.SqlClient.SqlConnection]::ClearAllPools()

So an update, I did get passed the error now, however, the problem now i am facing is, during the backup process, it starts either deleting, or doesnt finish and disposes of the thread/pool before it finishes, it does however finish some, but the first few doesnt finish and deletes or removes the backups, here is the entire code, maybe i am calling on Dispose to soon, I did even try and put an if statement in there (If $runspace.Status.IsCompleted), so if true, then i would dispose of it, however when i add that in, it doesnt even get to do any backups, so i removed that and kept the foreach within a foreach, but again doesnt seem to do or finish all the backups… below is the code, any thoughts?

 

1 Create and open runspace pool

$pool = [RunspaceFactory]::CreateRunspacePool(1,5)
$pool.ApartmentState = “MTA”
$pool.Open()
$runspaces = @()

2 Create reusable scriptblock

$scriptblock = {
Param(
[string] $DBName
,[string] $BackupType
,[string] $BackupPath
)

Process
{
$Date = Get-Date -Format “yyyy_MM_dd_HH_mm”
$StringDate = $Date.ToString()

If($BackupType -eq ‘Trn’)
{
$SQLStatement = “BACKUP LOG [” + $DBName + “] TO DISK = N’” + $BackupPath + ""+ $DBName + “" + $StringDate + “.trn’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}
ElseIf($BackupType -eq ‘Diff’)
{
$SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N’” + $BackupPath + ""+ $DBName + "
” + $StringDate + “.Diff’ WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}
Else
{
$SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N’” + $BackupPath + ""+ $DBName + “_” + $StringDate + “.bak’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}

Invoke-Sqlcmd -Query $SQLStatement -ServerInstance “Server001”
}

}

4 Add to Runspace Pool

5 Add runspace to runspace collection

ForEach ($DB in $DBList)
{
#$script = “scriptblock -DBName “” + $DB.name + “” -BackupType “” + $BackupType + “” -BackuPath “” + $BackupPath + “””

3 Create runspace and start it

$runspace = [PowerShell]::Create()#.AddScript($scriptblock).AddArgument($DB.name).AddArgument($BackupType.ToString()).AddArgument($BackupPath.ToString())
#$runspace = [PowerShell]::Create()
[void]$runspace.AddScript($scriptblock)
[void]$runspace.AddArgument($DB.name)
[void]$runspace.AddArgument($BackupType.ToString())
[void]$runspace.AddArgument($BackupPath.ToString())
$runspace.RunspacePool = $pool
$runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }

foreach ($runspace in $runspaces) {

#[void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
$runspace.Pipe.Dispose()

}
}

$pool.Close()
$pool.Dispose()

Cleanup SQL Connections

 

For better readability, please format your code be reading below thread.

https://powershell.org/forums/topic/read-me-before-posting-youll-be-glad-you-did/

Redone per format:

 

So an update, I did get passed the error now, however, the problem now i am facing is, during the backup process, it starts either deleting, or doesnt finish and disposes of the thread/pool before it finishes, it does however finish some, but the first few doesnt finish and deletes or removes the backups, here is the entire code, maybe i am calling on Dispose to soon, I did even try and put an if statement in there (If $runspace.Status.IsCompleted), so if true, then i would dispose of it, however when i add that in, it doesnt even get to do any backups, so i removed that and kept the foreach within a foreach, but again doesnt seem to do or finish all the backups… below is the code, any thoughts?

[pre]

1 Create and open runspace pool

$pool = [RunspaceFactory]::CreateRunspacePool(1,5)
$pool.ApartmentState = “MTA”
$pool.Open()
$runspaces = @()

2 Create reusable scriptblock

$scriptblock = {
Param(
[string] $DBName
,[string] $BackupType
,[string] $BackupPath
)

Process
{
$Date = Get-Date -Format “yyyy_MM_dd_HH_mm”
$StringDate = $Date.ToString()

If($BackupType -eq ‘Trn’)
{
$SQLStatement = “BACKUP LOG [” + $DBName + “] TO DISK = N’” + $BackupPath + “\”+ $DBName + “” + $StringDate + “.trn’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}
ElseIf($BackupType -eq ‘Diff’)
{
$SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N’” + $BackupPath + “\”+ $DBName + “
” + $StringDate + “.Diff’ WITH DIFFERENTIAL , NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}
Else
{
$SQLStatement = “BACKUP DATABASE [” + $DBName + “] TO DISK = N’” + $BackupPath + “\”+ $DBName + “_” + $StringDate + “.bak’ WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10”
}

Invoke-Sqlcmd -Query $SQLStatement -ServerInstance “Server001”
}

}

4 Add to Runspace Pool

5 Add runspace to runspace collection

ForEach ($DB in $DBList)
{
#$script = “scriptblock -DBName “” + $DB.name + “” -BackupType “” + $BackupType + “” -BackuPath “” + $BackupPath + “””

3 Create runspace and start it

$runspace = [PowerShell]::Create()#.AddScript($scriptblock).AddArgument($DB.name).AddArgument($BackupType.ToString()).AddArgument($BackupPath.ToString())
#$runspace = [PowerShell]::Create()
[void]$runspace.AddScript($scriptblock)
[void]$runspace.AddArgument($DB.name)
[void]$runspace.AddArgument($BackupType.ToString())
[void]$runspace.AddArgument($BackupPath.ToString())
$runspace.RunspacePool = $pool
$runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }

foreach ($runspace in $runspaces) {

#[void]$runspace.Pipe.EndInvoke($runspace.Status) # EndInvoke method retrieves the results of the asynchronous call
$runspace.Pipe.Dispose()

}
}

$pool.Close()
$pool.Dispose()

Cleanup SQL Connections

[/pre]

In powershell 7, you can use foreach-object -parallel instead, and it’s the same idea.

How do you edit your own posts? I was going to add start-threadjob as well.