Powershell SMO Asynchronous Backup and register-objectEvent

• Woody-458823

  Mr or Mrs. 500

  Points: 515

  More actions

  August 30, 2010 at 2:13 pm

  #225482

  I started using Powershell a couple of months ago to accomplish a lot of my administrative tasks. I found I can backup all of the databases for an instance asynchronously from one Powershell script. This has made it really convenient and has helped reduce total backup time.

  I currently poll the SMO.Backup.AsysncStatus to determine when a backup completes. However, I am now trying to use Powershell's Register-ObjectEvent to fire off an action when the backup completes. Unfortunately, I have not been able to capture the event. My code example is below. I call a function which creates a backup object, invokes the sqlasyncbackup method, and returns the backup object. The backup object is then used in the Register-ObjectEvent.

  Function Backup-SqlDatabase {

  Param(

  [string]$server,

  [string]$dbname,

  [switch]$asynch,

  [switch]$kill

  )

  $smo = New-Object Microsoft.SqlServer.Management.Smo.Server($server)

  $smo.ConnectionContext.StatementTimeout = 0

  $smo.ConnectionContext.ApplicationName = "PowerShell SMO"

  $dt = get-date -format yyyyMMdd-HHmmss

  $localdir = $smo.Settings.BackupDirectory + "\$($server.replace('\','$'))\$dbname\FULL"

  $bkfile = $($server -replace("\\", "$")) + "_" + $dbname + "_FULL_" + $dt + ".bak"

  If(-not(Test-Path $(Get-UncPath $server $localdir))) {

  New-Item $(Get-UncPath $server $localdir) -ItemType directory | Out-Null

  }

  $dbbk = new-object Microsoft.SqlServer.Management.Smo.Backup

  $dbbk.Action = 'Database'

  $dbbk.BackupSetDescription = "Full backup of " + $dbname

  $dbbk.BackupSetName = $dbname + " Backup"

  $dbbk.Database = $dbname

  $dbbk.MediaDescription = "Disk"

  $device = "$localdir\$bkfile"

  $dbbk.Devices.AddDevice($device, 'File')

  $dbbk.LogTruncation = [Microsoft.SqlServer.Management.Smo.BackupTruncateLogType]::Truncate

  If ($($kill.IsPresent)) {$smo.KillAllProcesses($dbname)}

  If ($asynch.IsPresent) { $dbbk.SqlBackupAsync($smo) }

  Else { $dbbk.SqlBackup($smo) }

  $dbbk

  }

  Clear-Host

  $bksuccess = [Microsoft.SqlServer.Management.Smo.ExecutionStatus]::Succeeded

  $server = "xxxsqlT01"

  $dbname = "MyDatabase"

  $backups = @()

  Write-Host "Starting Backup"

  $dbbk = Backup-SqlDatabase -server $server -dbname $dbname -asynch

  Try {

  Register-ObjectEvent $dbbk Complete -Action {

  [Console]::Beep(100,100)

  Write-Host "Job #$($sender.Id) ($($sender.Name)) complete." }

  }

  Catch {

  $err = $Error[0]

  Write-Host $err.Message

  }

  Do {

  Get-Event

  Write-Host $dbbk.AsyncStatus.ExecutionStatus

  if ($dbbk.AsyncStatus.ExecutionStatus -eq 'Succeeded') {

  Break }

  if ($dbbk.AsyncStatus.ExecutionStatus -eq 'Failed') {

  Throw $dbbk.AsyncStatus.LastException }

  Start-Sleep -Seconds 2

  }

  until ( 1 -ne 1 )

  Remove-Variable dbbk

  The database backs up just fine and the event registration seems to have worked (evidenced by the "job" Powershell creates to monitor with), but the associated action never occurs.

  Has anyone else even tried this and had any luck?