Page 4 of 4

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

Posted: Sun Jun 17, 2018 5:02 am
by Pallas
jvierra wrote:
Sat Jun 16, 2018 12:29 pm
or you must poll the table in SQS to get the info.

This should cause the call to wait until the backup has completed. I suspect that currently your job is completing almost instantaneously so there can be no progress output.
You are doing an incredible job. Thank you for your help. I do want to pull this information from a DMV.

Code: Select all

$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'"

The PowerShell Code I do have works, but I need to figure out two things, which I could not yet achive:

1. How to run two jobs simultaneously. Examples for this are suprisingly hard to find The SQL Query job needs a param $BackupSetNamecould so it returns the percentage of the correct backup job.
2. Display the result (percent) of the SQL Query in the richtextbox (or a progress bar) or whatever.
I figure

Code: Select all

$result= $results = Receive-Job -Job $Job -Keep| Select Percent_Complete
but I never managed to get this working either.

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

Posted: Sun Jun 17, 2018 8:32 am
by jvierra
1. Display the result (percent) of the SQL Query in the richtextbox (or a progress bar) or whatever.

As I posted a very long time ago. What you are trying to do cannot be done with PowerShell.
1. How to run two jobs simultaneously. Examples for this are suprisingly hard to find The SQL Query job needs a param $BackupSetNamecould so it returns the percentage of the correct backup job.
Just start a second job at any time. The JobTracker can run many jobs simultaneously.

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

Posted: Tue Jun 19, 2018 2:58 pm
by jvierra
I just discovered that we can send async results back from a job. This can be retrieved during the "UpdateScript".

Code: Select all

$sb = {
    $Database ='master'
    $BackupSetName = 'testser33'
    $FilePath = 'd:\testdb\testdb.bkp'
    $Server = 'Alpha'
    [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 = "Full (copyonly)backup of $Database File = $FilePath"
    $smoBackup.CompressionOption = 1
    $smoBackup.PercentComplete = 10
    $smoBackup.Devices.AddDevice($FilePath, 'File')
    
    $complete = Register-ObjectEvent $smoBackup Complete -Action {
          $global:bRunning = $false
    }
    $percent = Register-ObjectEvent $smoBackup PercentComplete -Action {
        Write-Progress -PercentComplete $smoBackup.PercentComplete -Activity Backup -Status Running
    }
    $info = Register-ObjectEvent $smoBackup Information -Action {
          $global:bRunning = $false
    }
    $smoBackup.PercentCompleteNotification = 1
    $global:bRunning = $true
    $smoBackup.SqlBackupAsync($Server)
    
    while($bRunning){  # waits until the backup signals complete
       sleep -Milliseconds 200
    } 
}

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

Posted: Wed Jun 20, 2018 12:22 am
by Pallas
That is great! Thank you! I will add this asap. All the best.