PowerShell Progress Meter

In many of my past PowerShell scripts, I've included my own home-spun progress report in the results window. I like that a lot, but my mind was blown when I noticed that there is a "Write-Process" cmdlet...with some re-tweaking I made it rock and much more efficient.

So the situation where you would want to use this is if you are looping over a large dataset and it takes a while to bring back results (I often will use PowerShell to hit all the servers I control, get some information about them and return it to me (backup status, if they are VM's, configuration info, etc). It can take 20+ minutes to get to all the servers and it is nice to know how far along the process is; thats where this comes in handy.

Some things to keep in mind...You need to have some resultset to work with, it doesn't have to be a query, but it does have to be some kind of System.Array. After you generate the Array, run this bit of code...I'm calling my array "results":

<################################
 ##  SETUP PROGRESS VARIABLES  ##
 ################################>
$ResultsLen = $results.Length
$LastPercentComplete = 0
[datetime]$DateStart = Get-Date -format G

This sets up some of the variables we will need inside the progress meter. Next, just after you issue your "foreach ($result in $results) {" You run the following code:

<##########################
 ##  RUN PROGRESS METER  ##
 ##########################>
foreach ($result in $results) 
{
 $currentRow++
 $PercentComplete = [math]::floor(($CurrentRow/$ResultsLen)*100)
 [datetime]$DateNow = Get-Date -format G
 $diff = $DateNow-$DateStart
 $DiffSeconds = $diff.TotalSeconds
 $SecondsPerRecord = $DiffSeconds/$currentRow
 $RemainingSeconds = $SecondsPerRecord*($ResultsLen-$currentRow)
 $SecondsPerRecord = [Math]::Round($SecondsPerRecord,2)
 
 Write-Progress -Activity "Investigating Servers..." `
  -PercentComplete $PercentComplete `
  -SecondsRemaining $RemainingSeconds `
  -CurrentOperation "Current Row: $currentRow / $ResultsLen.   Seconds/Record: $SecondsPerRecord   Run Time (seconds): $DiffSeconds" `
  -Status "Please wait."

 Remove-Variable DateNow
 Remove-Variable DiffSeconds
 Remove-Variable SecondsPerRecord
 Remove-Variable RemainingSeconds
}

You don't need to remove your variables, I just like to do it to clean up my code, I do this at the bottom of the script:

Remove-Variable ResultsLen
Remove-Variable currentRow
Remove-Variable LastPercentComplete
Remove-Variable DateStart

Want to see it in action? Take my script and add this to the top of it (creates an array with 10000 items, which should keep the status bar up long enough for you to see):
$results = 1..10000

This looks awesome in PowerGUI, which is what I use for PowerShell Development, it looks a bit funky in the native PS window, but good enough to get you the idea. If you aren't using PowerGUI, give it a shot, it's free. http://www.powergui.org

As always, please leave comments below if you find this useful or have other suggestions. It's nice to know people read these posts.

PowerShell - Check Backups

I wrote this script a while ago and have been meaning to publish it for others to use. It's pretty straight forward, just put in a Server\Instance and it will use SMO to check the Agent Job status, let you know if the Backup Job is running, or what the last outcome was. Then it queries the instance to see the last backup dates. We want daily backups, so we are interested in the hours since last backup occurred, so that is the final column for easy reference. The only thing you will need to tweak is the function that gets the Agent Job info. We have a specific naming convention that we use for all our backup jobs, if you do the same, you will need to edit the "WHERE" clause to meet your naming needs...perhaps simply $._Name -match "Backup" will work for you.

$LookupServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList localhost

<###############################################
 ## VARIABLES TO ENTER:                       ##
 ## LooupServer: Server\Instance to check  ##
 ###############################################>
$LookupServer = "localhost\DEV"

<###################################
 ## FUNCTION TO CHECK JOB STATUS  ##
 ###################################>
Function Get-SQLJobStatus
{
    param ([string]$server)
    # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    # Create object to connect to SQL Instance
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server

    # used to allow piping of more than one job name to function
    $srv.JobServer.Jobs | where {$_.Name -match "Backup" -and $_.Name -notmatch "LOG" -and $_.Name -notmatch "System" -and $_.IsEnabled -eq "True" -and $_.Name -notmatch "Optimizations" -and $_.Name -notmatch "History" -and $_.HasSchedule -eq "True"} | Select Name, CurrentRunStatus, LastRunOutcome,LastRunDate | Out-GridView
 Remove-Variable srv
 Remove-Variable server
}

<#############################################
 ## FUNCTION TO GET SERVER VERSION/EDITION  ##
 #############################################>
Function Get-SQLServerVersion
{
    param ([string]$server)
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    # Create object to connect to SQL Instance
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server

 $EditionName = $srv.Edition
 ##$srv.Version.Major
 ##$srv.VersionMinor
 switch ($srv.Version.Major) 
    { 
        8 {$VersionName = "2000"} 
        9 {$VersionName = "2005"} 
        10 {$VersionName = "2008"} 
        11 {$VersionName = "2012"} 
        default {$VersionName = "The version could not be determined."}
    }
 if ($srv.Version.Major -eq "10")
 {
  if ($srv.VersionMinor -eq "50")
  {
   $VersionName += " R2"
  }
 }
 $VersionEditionName = "$VersionName $EditionName"
 
 $table = New-Object system.Data.DataTable "$TableName"
 $col1 = New-Object system.Data.DataColumn VersionName,([string])
 $col2 = New-Object system.Data.DataColumn VersionEditionName,([string])
 
 $table.columns.add($col1)
 $table.columns.add($col2)
 
 $row = $table.NewRow();
 $row.VersionName = $VersionName ;
 $row.VersionEditionName = $VersionEditionName ;
 
 $table.Rows.Add($row) 

 $table
 
 Remove-Variable srv
 Remove-Variable server
 Remove-Variable EditionName
 Remove-Variable VersionName
 Remove-Variable VersionEditionName
 Remove-Variable table
 Remove-Variable col1
 Remove-Variable col2
 Remove-Variable row
}

CLS
$sd = Get-Date -format g
Write-Host "Starting: " $sd
Write-Host ""
$ServerInfo = Get-SQLServerVersion -server $LookupServer
$ServerVersionEdition = $ServerInfo.VersionEditionName
Write-Host "starting query for server: $LookupServer  version: $ServerVersionEdition "

if ($ServerInfo.VersionName -eq "2000")
{
 $BackupQuery = "
 SELECT '$($result.InstanceName)' AS [Server], DBS.name AS DBName,ISNULL(BKS.backup_size,0) AS backup_size,ISNULL(BKS.backup_size,0) AS compressed_backup_size
   ,ISNULL(BCKFMLY.device_type,0) AS device_type,BKS.backup_start_date,BKS.backup_finish_date
   , GETDATE() AS DateChecked, DATEDIFF(hour, BKUP.LastBackupDate, GETDATE()) AS HoursSinceLastBackup
 FROM sysdatabases DBS
 LEFT JOIN (
    SELECT '$($result.InstanceName)' AS [server]
      ,BK.database_name AS DBName 
      ,MAX(BK.backup_finish_date) AS LastBackupDate
      ,GETDATE() AS DateChecked
    FROM msdb.dbo.backupset BK
    LEFT JOIN msdb..backupmediafamily LBCKFMLY ON BK.media_set_id = LBCKFMLY.media_set_id
    WHERE BK.[type] = 'D'
      AND ISNULL(LBCKFMLY.physical_device_name,'\') LIKE '%\%'
      AND ISNULL(LBCKFMLY.physical_device_name,'\') NOT LIKE '{%'
    GROUP BY database_name
   ) BKUP  ON BKUP.DBName = DBS.name
 LEFT JOIN msdb.dbo.backupset BKS ON DBS.name = BKS.database_name AND BKUP.LastBackupDate = BKS.backup_finish_date AND BKS.[type] = 'D'
 LEFT JOIN msdb..backupmediafamily BCKFMLY ON BKS.media_set_id = BCKFMLY.media_set_id
 WHERE DBS.crdate < DATEADD(hour,-24,GETDATE()) 
   AND DBS.name NOT IN ('tempdb')
   AND DATABASEPROPERTYEX(DBS.name, 'Status') = 'ONLINE'
   AND ISNULL(BCKFMLY.physical_device_name,'\') NOT LIKE '{%'  
   AND ISNULL(BCKFMLY.physical_device_name,'\') LIKE '%\%' 
 ORDER BY bkup.DBName
 "
}
elseif ($ServerInfo.VersionName -eq "2005")
{
 $BackupQuery = "
 SELECT '$($result.InstanceName)' AS [Server], DBS.name AS DBName,ISNULL(BKS.backup_size,0) AS backup_size,ISNULL(BKS.backup_size,0) AS compressed_backup_size
   ,ISNULL(BCKFMLY.device_type,0) AS device_type,BKS.backup_start_date,BKS.backup_finish_date
   , GETDATE() AS DateChecked, DATEDIFF(hour, BKUP.LastBackupDate, GETDATE()) AS HoursSinceLastBackup
 FROM sys.databases DBS
 OUTER APPLY (
     SELECT BK.database_name ,
       MAX(BK.backup_finish_date) AS LastBackupDate
     FROM msdb.dbo.backupset BK
     LEFT JOIN msdb..backupmediafamily LBCKFMLY ON BK.media_set_id = LBCKFMLY.media_set_id
     WHERE DBS.name = BK.database_name
       AND BK.[type] = 'D'
       AND ISNULL(LBCKFMLY.physical_device_name,'\') LIKE '%\%'
       AND ISNULL(LBCKFMLY.physical_device_name,'\') NOT LIKE '{%'
     GROUP BY BK.database_name
    ) AS BKUP
 LEFT JOIN msdb.dbo.backupset BKS ON BKUP.database_name = BKS.database_name AND BKUP.LastBackupDate = BKS.backup_finish_date AND BKS.[type] = 'D'
 LEFT JOIN msdb..backupmediafamily BCKFMLY ON BKS.media_set_id = BCKFMLY.media_set_id
 WHERE DBS.create_date < DATEADD(hour,-24,GETDATE())
   AND DBS.state_desc = 'ONLINE'
   AND DBS.name NOT IN ('tempdb')
   AND ISNULL(BCKFMLY.physical_device_name,'\') NOT LIKE '{%'  
   AND ISNULL(BCKFMLY.physical_device_name,'\') LIKE '%\%' 
 ORDER BY DBS.name

 "
}
else
{
 $BackupQuery = "
 SELECT '$($result.InstanceName)' AS [Server], DBS.name AS DBName,ISNULL(BKS.backup_size,0) AS backup_size,ISNULL(BKS.backup_size,0) AS compressed_backup_size
   ,ISNULL(BCKFMLY.device_type,0) AS device_type,BKS.backup_start_date,BKS.backup_finish_date
   , GETDATE() AS DateChecked, DATEDIFF(hour, BKUP.LastBackupDate, GETDATE()) AS HoursSinceLastBackup
 FROM sys.databases DBS
 OUTER APPLY (
     SELECT BK.database_name ,
       MAX(BK.backup_finish_date) AS LastBackupDate
     FROM msdb.dbo.backupset BK
     LEFT JOIN msdb..backupmediafamily LBCKFMLY ON BK.media_set_id = LBCKFMLY.media_set_id
     WHERE DBS.name = BK.database_name
       AND BK.[type] = 'D'
       AND ISNULL(LBCKFMLY.physical_device_name,'\') LIKE '%\%'
       AND ISNULL(LBCKFMLY.physical_device_name,'\') NOT LIKE '{%'
     GROUP BY BK.database_name
    ) AS BKUP
 LEFT JOIN msdb.dbo.backupset BKS ON BKUP.database_name = BKS.database_name AND BKUP.LastBackupDate = BKS.backup_finish_date AND BKS.[type] = 'D'
 LEFT JOIN msdb..backupmediafamily BCKFMLY ON BKS.media_set_id = BCKFMLY.media_set_id
 WHERE DBS.create_date < DATEADD(hour,-24,GETDATE())
   AND DBS.state_desc = 'ONLINE'
   AND DBS.name NOT IN ('tempdb')
   AND ISNULL(BCKFMLY.physical_device_name,'\') NOT LIKE '{%'  
   AND ISNULL(BCKFMLY.physical_device_name,'\') LIKE '%\%' 
 ORDER BY DBS.name
 "
}
try
{
 Get-SQLJobStatus -server $LookupServer
 $BackupResults = Invoke-Sqlcmd -Query $BackupQuery -ServerInstance $LookupServer -Database master -ErrorAction Stop;
 $BackupResults | Out-GridView
 Remove-Variable BackupResults
 Remove-Variable BackupQuery
}
catch
{
 Write-OutputHighlighted -inputText "Error connecting to server " $LookupServer
}
$ed = Get-Date -format g
Write-Host "
Complete: " $ed

<#######################
 ## CLEANUP VARIABLES ##
 #######################>
Remove-Variable sd
Remove-Variable ed
Remove-Variable LookupServer
Remove-Variable ServerInfo
Remove-Variable ServerVersionEdition

You can see that we have broken out the query to check msdb for last backup info into three different queries, based on if the server is 2000, 2005 or 2008+ This is done due to the differences in views with the different versions.