Button - Start Job, how to multiple arguments, how to work with objects

[Pallas]

Hi,

Although I read carefully "PowerShell Studio: Creating Responsive Forms" and the two topics concerning this issue "Job Tracker help needed" and "Button - Start Job, how to pass arguments" I can not figure out how to pass my working code (click event) to your Button - Start Job click event.
I need to pass a bunch of (gloabl) variables + I need to build a new SMObackup object. I figured that I need to fill -ArgumentList and use Param ($Argument1) #Pass any arguments using the ArgumentList parameter. I am lost here. Any help would be greatly appreciated.

My working Code. But it is synchronous, which is not of any help when a db backup takes hours:

Code: [Select all] [Expand/Collapse]

1. $buttonBackupDB_Click= {   
2.  
3.     $Global:FileName = $textbox1_FN.Text
4.  
5.     $Server = $global:MSSQLServer
6.  
7.     $Database = $Global:SelectedDBName
8.  
9.     $Path = $Global:Path
10.  
11.     $Folder = $Global:CC + '\'
12.  
13.     $FileName = $Global:FileName
14.  
15.     $BackupSetName = $Global:BackupSetName
16.  
17.     $Extension = '.bak'
18.  
19.     $FilePath = $Path + $Folder + $FileName + $Extension
20.  
21.     $labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
22.  
23.     #ab hier smoBackup Objekt
24.  
25.     $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
26.  
27.     $smoBackup.Action = "Database"
28.  
29.     $smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
30.  
31.     $smoBackup.BackupSetName = $BackupSetName
32.  
33.     $smoBackup.Database = $Database
34.  
35.     $smoBackup.Copyonly = "True"
36.  
37.     $smoBackup.MediaDescription = "Disk"
38.  
39.     $smoBackup.CompressionOption = "1"
40.  
41.     $smoBackup.PercentCompleteNotification = "10"
42.  
43.     $smoBackup.Devices.AddDevice($FilePath, "File")
44.  
45.        
46.  
47.     Try
48.  
49.     {
50.  
51.         $smoBackup.SqlBackup($server)          
52.  
53.     }  
54.  
55.     Catch
56.  
57.     {
58.  
59.         $textbox_out.Text = $_.Exception.InnerException
60.  
61.     }
62.  
63.     <#$Global:FileName = $textbox1_FN.Text
64.  
65.     $Server = $global:MSSQLServer
66.  
67.     $Database = $Global:SelectedDBName
68.  
69.     $Path = $Global:Path
70.  
71.     $Folder = $Global:CC + '\'
72.  
73.     $FileName = $Global:FileName
74.  
75.     $Extension = '.bak'
76.  
77.     $FilePath = $Path + $Folder + $FileName + $Extension
78.  
79.     $labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut#>

GeSHi © Codebox Plus Extension

[/size][/size]

Not working:

Code: [Select all] [Expand/Collapse]

1. [size=85]$buttonStartJob_Click={
2.  
3.    
4.  
5.     $buttonStartJob.Enabled = $false   
6.  
7.    
8.  
9.     #Create a New Job using the Job Tracker
10.  
11.     Add-JobTracker -Name 'BackUpDBJob' `
12.  
13.                    -JobScript {
14.  
15.         #--------------------------------------------------
16.  
17.         #TODO: Set a script block
18.  
19.         #Important: Do not access form controls from this script block.
20.  
21.        
22.  
23.         #ab hier smoBackup Objekt
24.  
25.         $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
26.  
27.         $smoBackup.Action = "Database"
28.  
29.         $smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
30.  
31.         $smoBackup.BackupSetName = $BackupSetName
32.  
33.         $smoBackup.Database = $Database
34.  
35.         $smoBackup.Copyonly = "True" #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
36.  
37.         $smoBackup.MediaDescription = "Disk"
38.  
39.         $smoBackup.CompressionOption = "1"
40.  
41.         $smoBackup.PercentCompleteNotification = "10"
42.  
43.         $smoBackup.Devices.AddDevice($FilePath, "File")
44.  
45.         $smoBackup.SqlBackup($server)
46.  
47.        
48.  
49.         Param ($Argument1) #Pass any arguments using the ArgumentList parameter
50.  
51.        
52.  
53.        
54.  
55.        
56.  
57.         #--------------------------------------------------
58.  
59.     }`
60.  
61.     $env:FileName = $textbox1_FN.Text
62.  
63.     $env:Server = $global:MSSQLServer
64.  
65.     $wnv:Database = $Global:SelectedDBName
66.  
67.     $Path = $Global:Path
68.  
69.     $Folder = $Global:CC + '\'
70.  
71.     $FileName = $Global:FileName
72.  
73.     $BackupSetName = $Global:BackupSetName
74.  
75.     $Extension = '.bak'
76.  
77.     $FilePath = $Path + $Folder + $FileName + $Extension
78.  
79.     $labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
80.  
81.     -Argumentlist #WHAT GOES HERE AND HOW?
82.  
83.    
84.  
85.     -CompletedScript {
86.  
87.         Param($Job)
88.  
89.         #$results = Receive-Job -Job $Job
90.  
91.         #Enable the Button
92.  
93.         $buttonStartJob.ImageIndex = -1
94.  
95.         $buttonStartJob.Enabled = $true
96.  
97.     }`
98.  
99.     -UpdateScript {
100.  
101.         Param($Job)
102.  
103.         #$results = Receive-Job -Job $Job -Keep
104.  
105.         #Animate the Button
106.  
107.         if($null -ne $buttonStartJob.ImageList)
108.  
109.         {
110.  
111.             if($buttonStartJob.ImageIndex -lt $buttonStartJob.ImageList.Images.Count - 1)
112.  
113.             {
114.  
115.                 $buttonStartJob.ImageIndex += 1
116.  
117.             }
118.  
119.             else
120.  
121.             {
122.  
123.                 $buttonStartJob.ImageIndex = 0     
124.  
125.             }
126.  
127.         }
128.  
129.     }`
130.  
131.    
132.  
133. }
134.  
135. [/size]

GeSHi © Codebox Plus Extension

[/i]

[mxtrinidad]

I would say the simplest way would be to build a scriptblock with the SMO backup code, then use the Start-Job to execute in the background. Then, the button execute the code until the process completes.

I will share some code I use for an Azure sample that may be adaptable to your app:

Code: Select all

$buttonRunSMOBackup_Click={
	#TODO: Place custom script here
		$DisplayText.Text = "Display Information`r`n";
	
	## - Setup job to execute in backgroundL:
	$jobScript = {
		## - SMO Backup Code below:
		:
		:
	};
	
	$DisplayText.AppendText("Running Job - SMO Backup`r`n");
	Start-Job -ScriptBlock $jobScript -Name SMOBackup1 
	
	## - Check for job completion:
	do
	{
		$DisplayText.AppendText("Checking SMO Backup Completion`r`n");
		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:
	$DisplayText.AppendText("Displaying Asynch Job Results:`r`n");
	$DisplayText.AppendText(($SMOJobResults | Format-Table | Out-String -Width 1000));
	
	## - Display Job result in Datagridview:
	$datagridview1.DataSource = ConvertTo-DataTable -InputObject $SMOJobResults;
	$DisplayText.AppendText("Processing Completed`r`n");
	$DisplayText.AppendText((Get-Date).ToString("MMddyyyy_HHmm"));
}

This sample code was meant to display the results to both RichTextBox and a Datagridview.
I'm hoping it will help in any way.

Let us know.

Just make sure your SMO Backup code runs before integrating it to the form and make sure DLL's are in place with the application or the system you're deploying the form.

[davidc]

[TOPIC MOVED TO POWERSHELL GUIS FORUM BY MODERATOR]

[Pallas]

I would say the simplest way would be to build a scriptblock with the SMO backup code, then use the Start-Job to execute in the background. Then, the button execute the code until the process completes.

...

That seems an elegant solution. So I sacrifice the use of Button - Start Job, take what is working, right now (myoldbutton click event) and build my own job, job-recieve, etc. This sample code looks promising. Thank you!!! I will work on this!

[mxtrinidad]

I have to say that using the "Button - Start Job" is useful. I use it to demo executing PowerShell Core in a SAPIEN Windows Forms. And now, I'm talking about cross-platform scripting possibilities.

Don't hesitate to contact us!

[Pallas]

works:

Code: [Select all] [Expand/Collapse]

1. [size=50]$buttonBackupDBJob_Click= {
2.  
3.     #hier wird die eigentliche Sicherung per Knopfdruck durchgeführt
4.  
5.    
6.  
7.     $Global:FileName = $textbox1_FN.Text
8.  
9.     $Server = $global:MSSQLServer
10.  
11.     $Database = $Global:SelectedDBName
12.  
13.     $Path = $Global:Path
14.  
15.     $Folder = $Global:CC + '\'
16.  
17.     $FileName = $Global:FileName
18.  
19.     $BackupSetName = $Global:BackupSetName
20.  
21.     $Extension = '.bak'
22.  
23.     $FilePath = $Path + $Folder + $FileName + $Extension
24.  
25.     $labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
26.  
27.     #ab hier smoBackup Objekt
28.  
29.     $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
30.  
31.     $smoBackup.Action = "Database"
32.  
33.     $smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
34.  
35.     $smoBackup.BackupSetName = $BackupSetName
36.  
37.     $smoBackup.Database = $Database
38.  
39.     $smoBackup.Copyonly = "True" #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
40.  
41.     $smoBackup.MediaDescription = "Disk"
42.  
43.     $smoBackup.CompressionOption = "1"
44.  
45.     $smoBackup.PercentCompleteNotification = "10"
46.  
47.     $smoBackup.Devices.AddDevice($FilePath, "File")
48.  
49.    
50.  
51.     $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup # SMO Backup Objekt
52.  
53.     $smoBackup.Action = "Database"
54.  
55.     $smoBackup.BackupSetDescription = "Full (copyonly)backup of $($Database)"
56.  
57.     $smoBackup.BackupSetName = $BackupSetName
58.  
59.     $smoBackup.Database = $Database
60.  
61.     $smoBackup.Copyonly = "True" #verändert die DatabaseBackupLSN und bringt die scheduled inkrementellen Backups nicht durcheinander
62.  
63.     $smoBackup.MediaDescription = "Disk"
64.  
65.     $smoBackup.CompressionOption = "1"
66.  
67.     $smoBackup.PercentCompleteNotification = "10"
68.  
69.     $smoBackup.Devices.AddDevice($FilePath, "File")
70.  
71.    
72.  
73.      $smoBackup.SqlBackup($server) 
74.  
75.    
76.  
77. }[/size]

GeSHi © Codebox Plus Extension

[/i]

[Pallas]

does NOT backup:
Error:
ERROR: Exception calling "SqlBackup" with "1" argument(s): "Backup operation failed."
ERROR: + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
ERROR: + FullyQualifiedErrorId : FailedOperationException
ERROR: + PSComputerName : localhost
ERROR:

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
	$labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut

	
	
	$richtextbox_out.Text = "Display Information`r`n";
	
	## - Setup job to execute in background:
	## - SMO Backup Code below:
	
	$jobScript = {
	#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)
	};
	
	$richtextbox_out.AppendText("Running Job - SMO Backup`r`n");
		Start-Job -ScriptBlock $jobScript -Name SMOBackup1
	## - Check for job completion:
	do
	{
		$richtextbox_out.AppendText("Checking SMO Backup Completion`r`n");
		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));
	
	## - Display Job result in Datagridview:
	#$datagridview1.DataSource = ConvertTo-DataTable -InputObject $SMOJobResults;
	$richtextbox_out.AppendText("Processing Completed`r`n");
	$richtextbox_out.AppendText((Get-Date).ToString("MMddyyyy_HHmm"));
}

[jvierra]

Run the script as a job at a command prompt until you have it fully debugged.

Note that none of the variables in the job script are defined. You have not created a Param statement and ther eis no "ArgumentList"

Code: Select all

$jobScript = {
    Param(
        $Database,
        $BackupSetName,
        $FilePath,
        $Server
    )
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
    $smoBackup = New-Object Microsoft.SqlServer.Management.Smo.Backup 
    $smoBackup.Action = 'Database'
    $smoBackup.BackupSetDescription = "Full (copyonly)backup of $Database"
    $smoBackup.BackupSetName = $BackupSetName
    $smoBackup.Database = $Database
    $smoBackup.Copyonly = $true
    $smoBackup.MediaDescription = 'Disk'
    $smoBackup.CompressionOption = 1
    $smoBackup.PercentCompleteNotification = 10
    $smoBackup.Devices.AddDevice($FilePath, 'File')
    $smoBackup.SqlBackup($Server)
}
$argList = @(
    $Database,
    $BackupSetName,
    $FilePath,
    $Server
)
Start-Job -ScriptBlock $jobScript -Name SMOBackup1 -ArgumentList $argList

[Pallas]

Hi,

I was on that argument list, but I would have missed the Param. I am still very new to this, but it works. Sort off. I ditched the data grid view, because it only returned errors and I managed to make the rich text box to scroll down. When backing up larger databases the application will close but stay minimized and never come back. At least if I run it from Power Shell Studio.
I will attach what I have for the record just in case it could help someone.
Things to do:

1. Take the working code and try to use Sapiens Start Job Button. I hope it will create a more user friendly expierience with long running backups. (Level: medium (for me)
2. I would love to get a progress bar running. If you have a great idea how to do that easy I would be glad. I do have some t-sql query which will return what the SQL Server thinks where your backup is. I would need to extract that percentage and put that into a progress bar (Level: very hard (for me))

SQL Code to query MS SQL Server for the state of backup (Name: $BackupSetName)

Code: Select all

$button1_Click={
	#TODO: Place custom script here
	$Server = $global:MSSQLServer
	$Database = $Global:SelectedDBName
	$BackupSetName =$Global:BackupSetName
	$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'"
	$myResult = Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $SQLQuery
	$richtextbox_out.Text = $myResult
	
}

----------------------------------------------------------Working code-------------------------------------

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
	$labelPathToBackupFile.Text = $FilePath #Debug welchen Pfad habe ich zusammen gebaut
	
	
	$richtextbox_out.Text = "Display Information`r`n";
	
	## - 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
	)
	$richtextbox_out.AppendText("Running Job - SMO Backup`r`n");
	Start-Job -ScriptBlock $jobScript -Name SMOBackup1 -ArgumentList $argList
	## - Check for job completion:
	do
	{
		$richtextbox_out.AppendText("Checking SMO Backup Completion`r`n - still running");
		$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()
	
	## - Display Job result in Datagridview:
	#$datagridview1.DataSource = ConvertTo-DataTable -InputObject $SMOJobResults;
	$richtextbox_out.AppendText("Processing Completed`r`n");
	$richtextbox_out.AppendText((Get-Date).ToString("dd_MMMM_yyyy_HHmm"));
	$richtextbox_out.ScrollToCaret()
}

[jvierra]

There is no way to do a progress bar with a blocking process. That is why we use the JobTracker controls.

In Net classes we do not use quoted integers and we use $true/$false for Booleans and NOT "true" or "false".

The backup does not report percentage if you do not use "SqlBackupAsync", The percentage tells how often to raise the progress event. The event is not available in Forms. To use this in a PowerShell form you would have to write a C# module and call it from a separate runspace that can call back to the main form.

You can run the code async without the event by using $bacup.Wait() which will allow database updates and prevent the job from completing before the backup is finished.