$ErrorActionPreference = "Stop" #stop when an error is encountered
#declare variables
$server = "YOURSERVER"
$database = "master"
$query = @"
sp_databases
"@
$querytimeout = 0 #0 means forever, change it as needed
$objectExclude = 'tempdb, northwind, pubs' -split ", " #databases to not backup
$objectNameField = "DATABASE_NAME" #this one is returned by sp_databases
$objectSortExpression = @{Expression={$_[1]}} #DATABASE_SIZE returned by sp_databases, add Ascending=$false to start with the largest
$SleepTimer = 1000 #after X milliseconds, check if the jobs have finished. 1000 is every second.
$MaxResultTime = 7200 #after X seconds, all jobs are killed. 7200 is two hours.
$tasks = @(# taskOrder, taskName, maxThreads, scriptToRun) args0=databaseName. Only one command.
	,@(1, 'backup', 1, 'sqlcmd -Q "BACKUP DATABASE [$args0] TO DISK=N''C:\${args0}.bak'' WITH INIT" -r0 -b')
	,@(2, 'store', 1, 'Copy-Item -Path "C:\${args0}.bak" -Destination "\\archive\c$\${args0}.bak"')
	,@(3, 'delete', 1, 'Remove-Item "C:\${args0}.bak"')
)
$startAtTask = 1 #i.e. if the network was unavailable, you may need to re-run starting from that task
#import modules
Import-Module SqlPs -DisableNameChecking #uncomment for running it directly in a ps command prompt
$error.clear() #clear error generated by last command
#get list of databases and sort
$objects = @((Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -querytimeout $queryTimeout) | where {$objectExclude -notcontains $_.$objectNameField} | sort $objectSortExpression )
#environment setup
$RunspacePools = @()
$Jobs = @()
$ISS = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$taskInfo = @{} # key=taskOrder; value=nextTaskOrder
$maxTaskOrder = -1
$objectInfo = @{} # key=taskId; values=each database
$objectInfoArr = @(0) * $objects.length
$output = ""
$errors = ""
$errorCount = @{}
for ($i=0; $i -lt $tasks.length; $i++) {
	$RunspacePools += [runspacefactory]::CreateRunspacePool(1, $tasks[$i][2] <# maxThreads #>, $ISS, $Host)
	$RunspacePools[$i].Open()
	if ($taskInfo.Count -eq 0 -Or -Not $taskInfo.ContainsKey($tasks[$i][0])) { #taskOrder
		$taskInfo.Add($tasks[$i][0], -1)
		if ($tasks[$i][0] -gt $maxTaskOrder) { $maxTaskOrder = $tasks[$i][0] }
	}
	$objectInfo.Add($i, $objectInfoArr.clone())
	$tasks[$i][3] = '$ErrorActionPreference = "Stop"; $args0 = $args[0]; try { $output = ' + $tasks[$i][3] + ' 2>&1 } catch { $err = $_.Exception; $errors = $err.Message; while($err.InnerException) { $err = $err.InnerException; $errors += "|" + $err.Message } } $LastExitCode; $errors | where { $_ } | Out-String; $output | where { $_ } | Out-String'
	$errorCount.Add($i, 0)
}
foreach ($key in @($taskInfo.Keys)) { $taskInfo[$key] = ($taskInfo.Keys | where {$_ -gt $key} | sort | select -First 1) }
#function to create thread and start processing
function CreateThread() {
	param ([string]$objectName, [int]$objectIndex, [int]$taskIndex, [int]$taskOrder, [ref]$Jobs)
	$PowershellThread = [powershell]::Create().AddScript($tasks[$taskIndex][3]) #scriptToRun
	$PowershellThread.AddArgument($objectName) | out-null
	$PowershellThread.RunspacePool = $RunspacePools[$taskIndex]
	$Handle = $PowershellThread.BeginInvoke()
	$Job = "" | select Handle, Thread, ObjectName, ObjectIndex, TaskIndex, TaskOrder, object
	$Job.Handle = $Handle; $Job.Thread = $PowershellThread
	$Job.ObjectName = $objectName; $Job.ObjectIndex = $objectIndex; $Job.TaskIndex = $taskIndex; $Job.TaskOrder = $taskOrder
	$Jobs.value += $Job
}
$ResultTimer = Get-Date
#start processing first task for each database
for ($i=0; $i -lt $objects.length; $i++) {
	$object = $objects[$i].$objectNameField
	$tasks | where {$_[0] -eq $startAtTask} | foreach { CreateThread $object $i ([array]::IndexOf($tasks, $_)) $_[0] ([ref]$Jobs) }
}
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
	#update completed jobs and dispose them
	foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
		#update status
		$objectInfo[$Job.TaskIndex][$Job.ObjectIndex] = 1
		#get results. 0=LastExitCode (must be 0 or null), 1=$errors (must be empty), 2=$output (may be empty)
		$results = $Job.Thread.EndInvoke($Job.Handle)
		$errors += $results[1] + "`r`n"
		$output += $results[2] + "`r`n"
		if (($results[0] -and $results[0] -ne 0) -or $results[1] -ne "") { $errorCount[$Job.TaskIndex] += 1; $objectInfoArr[$Job.ObjectIndex] = 1; }
		#launch next task
		if ($Job.TaskOrder -lt $maxTaskOrder -and #there are pending tasks
			(@($Jobs | where {$_.TaskOrder -eq $Job.TaskOrder -and #same taskOrder
								 $_.ObjectName -eq $Job.ObjectName -and #same database
								 $_.Handle.IsCompleted -eq $False}).count -eq 0) -and #no active threads
			$objectInfoArr[$Job.ObjectIndex] -eq 0) { #no errors so far
			$tasks | where {$_[0] -eq $taskInfo[$Job.TaskOrder]} | foreach { CreateThread $Job.ObjectName $Job.ObjectIndex ([array]::IndexOf($tasks, $_)) $_[0] ([ref]$Jobs) }
		}
		#end thread
		$Job.Thread.Dispose()
		$Job.Thread = $Null
		$Job.Handle = $Null
	}
	#show progress
	for ($i=0; $i -lt $tasks.length; $i++) {
		$inProgress = @($Jobs | where {$_.TaskIndex -eq $i -and $_.Handle.IsCompleted -eq $False}).count
		$failed = $errorCount[$i]
		Write-Progress `
			-Id $i `
			-Activity $tasks[$i][1] `
			-PercentComplete (($objectInfo[$i] | measure-object -Sum).Sum * 100 / $objects.length) `
			-Status "$inProgress in progress, $failed failed"
	}
	#exit on timeout
	$currentTime = Get-Date
	if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
		Write-Error "Child script appears to be frozen, try increasing MaxResultTime"
		break
	}
	#sleep
	Start-Sleep -Milliseconds $SleepTimer
}
#dispose thread pools
for ($i=0; $i -lt $tasks.length; $i++) {
	$RunspacePools[$i].Close() | Out-Null
	$RunspacePools[$i].Dispose() | Out-Null
}
if (($errors -replace "`r`n", "") -ne "") { throw $errors + " " + $output } else { $output }