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