Powershell +SQL Восстановление баз с прогрессом

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCatalog = "master";
$SqlLogin = "sa";
$arrayofDATA=@(
$(New-Object PSOBJECT -Property @{
PATH='C:\Temp\analit.sql'
SERVER="."
Pass="321"
}),
$(New-Object PSOBJECT -Property @{
PATH='C:\Temp\kassa.sql'
SERVER=".\KASSA"
Pass="123"
}))
 
foreach($data in $arrayofDATA)
{
    $SERV=$data.Server
    $PATH=$data.PATH
    $Pass=$data.Pass
    $SqlConnection.ConnectionString = "Server=$SERV; Database=$SqlCatalog; User ID=$SqlLogin; Password=$Pass;"
    $SqlConnection.Open()
    $SqlCmd = $SqlConnection.CreateCommand()
    $SqlCmd.CommandTimeout = 0;
    $SqlCmd.CommandText = [IO.File]::ReadAllText($PATH) ;
    $objReader = $SqlCmd.ExecuteReader()
    while ($objReader.HasRows)
{
 
    while ($objReader.read()) {
    echo $objReader.GetValue(0)
}
$objReader.NextResult();    
}
    $objReader.close()
    $SqlConnection.Close()
}

<#
  sqlps dependency
  If module sqlps does not exist, install from:
    Microsoft SQL Server 2016 Feature Pack ([url]https://www.microsoft.com/en-us/download/details.aspx?id=52676[/url])
    - SQLSysClrTypes.msi
    - SharedManagementObjects.msi
    - PowershellTools.msi
#>
 
param(
  [Parameter(Mandatory=$true,Position=0)]
  [string]$servername,
  [Parameter(Mandatory=$true,Position=1)]
  [string]$dbname,
  [Parameter(Mandatory=$true,Position=2)]
  [string]$filename)
 
 
 
Push-Location
Import-Module "sqlps" -DisableNameChecking
Pop-Location
 
<#
  MAIN
#>
 
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $servername
$restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
$device = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem $filename, "FILE"
$restore.Devices.Add($device)
try {
  $filelist = $restore.ReadFileList($server)
}
catch {
    $exception = $_.Exception
    Write-Host "$exception. `n`nDoes the SQL Server service account have acccess to the backup location?" -ForegroundColor Red
  exit 1
}
 
$filestructure = @{}; $datastructure = @{}; $logstructure = @{}
$logfiles = $filelist | Where-Object {$_.Type -eq "L"}
$datafiles = $filelist | Where-Object {$_.Type -ne "L"}
 
# Data Files (if db has filestreams, make sure server has them enabled)
$defaultdata = $server.DefaultFile
$defaultlog = $server.DefaultLog
if ($defaultdata.Length -eq 0) {
    $defaultdata = $server.Information.MasterDBPath
}
 
if ($defaultlog.Length -eq 0) {
    $defaultlog = $server.Information.MasterDBLogPath
}
 
foreach ($file in $datafiles) {
    $newfilename = Split-Path $($file.PhysicalName) -leaf
 
    $datastructure.physical = "$defaultdata$newfilename"
    $datastructure.logical = $file.LogicalName
    $filestructure.add($file.LogicalName,$datastructure)
}
 
# Log Files
foreach ($file in $logfiles) {
    $newfilename = Split-Path $($file.PhysicalName) -leaf
 
    $logstructure.physical = "$defaultlog$newfilename"
    $logstructure.logical = $file.LogicalName
    $filestructure.add($file.LogicalName,$logstructure)
}
 
# Make sure big restores don't timeout
$server.ConnectionContext.StatementTimeout = 0
 
foreach ($file in $filestructure.values) {
    $movefile = New-Object "Microsoft.SqlServer.Management.Smo.RelocateFile"
    $movefile.LogicalFileName = $file.logical
    $movefile.PhysicalFileName = $file.physical
    $null = $restore.RelocateFiles.Add($movefile)
}
 
Write-Host "Restoring $dbname to $servername" -ForegroundColor Yellow
 
# kill all connections
$server.KillAllProcesses($dbname)
 
try {
 
    $percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
        Write-Progress -id 1 -activity "Restoring $dbname to $servername" -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
    }
    $restore.add_PercentComplete($percent)
    $restore.PercentCompleteNotification = 1
    $restore.add_Complete($complete)
    $restore.ReplaceDatabase = $true
    $restore.Database = $dbname
    $restore.Action = "Database"
    $restore.NoRecovery = $false
 
  # take most recent backup set if there are more than one
  $restore.FileNumber = $restore.ReadBackupHeader($server).Rows.Count
 
    Write-Progress -id 1 -activity "Restoring $dbname to $servername" -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))
    $restore.sqlrestore($servername)
    Write-Progress -id 1 -activity "Restoring $dbname to $servername" -status "Complete" -Completed
 
    Write-Host "Restore complete!" -ForegroundColor Green
 
  exit 0
}
catch {
    $exception = $_.Exception.InnerException
  Write-Host $exception -ForegroundColor Red
  exit 1
}

$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 }