Another day another PowerShell Box of Tricks post

Auditors, managers and bosses often want proof of following processes successfully so when they come knocking on my door(I don’t have a door, it’s usually my shoulder they knock) asking when the last backups were taken I either use this function or show them the excel file my automated process creates. This depends on if I think the pretty colours in the excel sheet will impress them!

The Show-LastServerBackup function iterates through each database on the server and takes each of the three properties mentioned in yesterdays post. However this time I created an empty hash table and added each result to it as follows

I created the hash table with @() and then assign each property to a variable inside the loop and add it to a temporary PSObject with some custom NoteProperties to fit the data

The last line adds the temporary object to the hash table. I then simply pipe the hash table to Format-Table to show the results

Call it like this

Ooops I haven’t backed up my system databases! Luckily it is my Azure server for blogging and presenting and is torn down and recreated whenever it is needed

You can get the code here

#############################################################################################
#
# NAME: Show-LastServerBackup.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:06/08/2013
#
# COMMENTS: Load function for Showing Last Backup of each database on a server
# ————————————————————————
Function Show-LastServerBackup ($SQLServer) {

    $server = new-object "Microsoft.SqlServer.Management.Smo.Server" $SQLServer

    $Results = @();

    foreach ($db in $server.Databases) {
        $DBName = $db.name
        $LastFull = $db.lastbackupdate
        if ($lastfull -eq '01 January 0001 00:00:00')
        {$LastFull = 'NEVER'}

        $LastDiff = $db.LastDifferentialBackupDate  
        if ($lastdiff -eq '01 January 0001 00:00:00')
        {$Lastdiff = 'NEVER'}
                                                                                                                                                        
        $lastLog = $db.LastLogBackupDate 
        if ($lastlog -eq '01 January 0001 00:00:00')
        {$Lastlog = 'NEVER'}

        $TempResults = New-Object PSObject;
        $TempResults | Add-Member -MemberType NoteProperty -Name "Server" -Value $Server;
        $TempResults | Add-Member -MemberType NoteProperty -Name "Database" -Value $DBName;
        $TempResults | Add-Member -MemberType NoteProperty -Name "Last Full Backup" -Value $LastFull;
        $TempResults | Add-Member -MemberType NoteProperty -Name "Last Diff Backup" -Value $LastDiff;
        $TempResults | Add-Member -MemberType NoteProperty -Name "Last Log Backup" -Value $LastLog;
        $Results += $TempResults;
    }
    $Results
}