Re: Button - Start Job, how to multiple arguments, how to work with objects
Posted: Thu Jun 14, 2018 7:42 am
Thank you jvierra,
I fixed my errors that you pointed out. Working with the Button - Start Job was not getting me anywhere. So I returned to the sample code. Right now I am trying to run two jobs parallel. Asking SQL Server about the percentage of the backup job completion and write that to the rich text box.
I am stuck now on how to check the second job (do while loop) for its status.
Any advice?
What I have so far:
I fixed my errors that you pointed out. Working with the Button - Start Job was not getting me anywhere. So I returned to the sample code. Right now I am trying to run two jobs parallel. Asking SQL Server about the percentage of the backup job completion and write that to the rich text box.
I am stuck now on how to check the second job (do while loop) for its status.
Any advice?
What I have so far:
Code: Select all
$buttonBackupDBJob_Click= {
#hier wird die eigentliche Sicherung per Knopfdruck durchgeführt
$Global:FileName = $textbox1_FN.Text
$Server = $global:MSSQLServer
$Database = $Global:SelectedDBName
$Path = $Global:Path
$Folder = $Global:CC + '\'
$FileName = $Global:FileName
$BackupSetName = $Global:BackupSetName
$Extension = '.bak'
$FilePath = $Path + $Folder + $FileName + $Extension
$textbox_path.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
$richtextbox_out.Text = "Display Information`r`n";
$SQLQuery = "SELECT Session_id as SPID, Command , query.text AS 'Query Text', Start_Time, Percent_Complete, dateadd(second,total_elapsed_time/1000, getdate()) as 'Total Elapsed Time',
dateadd(second,estimated_completion_time/1000, getdate()) as 'Estimated Completion Time'
FROM sys.dm_exec_requests request CROSS APPLY sys.dm_exec_sql_text(request.sql_handle) query
WHERE request.command in ('BACKUP DATABASE') and query.text like '%WITH name=%$BackupSetName'"
$jobSQLScript = {
Param (
$Server,
$Database,
$BackupSetName,
$SQLQuery
)
$myResult = (Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $SQLQuery).Percent_Complete
#$richtextbox_out.Text = $myResult
}
## - Setup job to execute in background:
## - SMO Backup Code below:
$jobScript = {
Param (
$Database,
$BackupSetName,
$FilePath,
$Server
)
#Load SMO assemblies
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null #SMO #das SMO Objekt holen und den Output nach /dev/null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
$smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
$smoBackup.Action = "Database"
$smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
$smoBackup.BackupSetName = $BackupSetName
$smoBackup.Database = $Database
$smoBackup.Copyonly = $true #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
$smoBackup.MediaDescription = "Disk"
$smoBackup.CompressionOption = 1
#$smoBackup.PercentCompleteNotification = "10"
$smoBackup.Devices.AddDevice($FilePath, "File")
$smoBackup.SqlBackup($server) #Befehl der Sicherung wird ausgefuehrt
}
$argList = @(
$Database,
$BackupSetName,
$FilePath,
$Server
)
$argSQLList = @(
$Server,
$Database,
$BackupSetName,
$SQLQuery
)
$richtextbox_out.AppendText("Running Job - SMO Backup`r`n");
Start-Job -ScriptBlock $jobScript -Name SMOBackup1 -ArgumentList $argList
Start-Sleep -Seconds 5
Start-Job -ScriptBlock $jobSQLScript -Name SQLPercent -ArgumentList $argSQLList
## - Check for job completion:
do
{
$Counter = 1 + $Counter
$richtextbox_out.AppendText("Checking - still running`r`n")
$richtextbox_out.AppendText($Counter)
$richtextbox_out.ScrollToCaret()
Start-Sleep -Seconds 5
}
while ((Get-Job -Name 'SMOBackup1').State -ne 'Completed');
$result1 = Get-Job -Name 'SMOBackup1';
## - Display Job Status at EOJ:
[array]$SMOJobResults = (Get-Job -Name 'SMOBackup1' | Receive-Job);
## - Display Job Results in RichTextBox:
$richtextbox_out.AppendText("Displaying Asynch Job Results:`r`n");
$richtextbox_out.AppendText(($SMOJobResults | Format-Table | Out-String -Width 1000));
$richtextbox_out.ScrollToCaret()
$richtextbox_out.AppendText("Processing Completed`r`n");
$richtextbox_out.AppendText((Get-Date).ToString("dd_MMMM_yyyy_HHmm"));
$richtextbox_out.ScrollToCaret()
}