Powershell Script to Retrieve SQL Backup Status

GetDBBackupStatus.ps1

Import-Module "sqlps" -DisableNameChecking

Function Get-DBBackupInfo ($SQLInstance, $DBName)
 
{
 
    if ($SQLInstance.Contains("`\"))
 
        { $location = "SQLSERVER:\SQL\$SQLInstance\Databases" }
 
    else
 
        { $location = "SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases" }
 
   

   $DisplayResults =   @{Label="DB Name @ $SQLInstance" ;Expression={$_.Name};width=40},
 
        @{Label="Last Full";Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"NA"} 

            ELSE {$_.LastBackupDate.ToString("yyyy/MM/dd HH:mm:ss")}};width=25},
 
        @{Label="Last Log.backup";Expression={IF ($_.LastLogBackupDate -eq "01/01/0001 00:00:00") {"NA"}
 
             ELSE {$_.LastLogBackupDate.ToString("yyyy/MM/dd HH:mm:ss")}};width=20}, 

        @{Label="Most Recent Type";Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"NA"} 

            ELSEIF ($_.LastBackupDate -gt $_.LastLogBackupDate) {"FULL"} 

            ELSE {"LOG"}};width=20},
 
        @{Label="Days Since LastBackup";Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"Never Backed Up!"} 

            ELSEIF ($_.LastDifferentialBackupDate -gt $_.LastBackupDate) {((Get-Date) - $_.LastDifferentialBackupDate).Days} 

            ELSE {((Get-Date) - $_.LastBackupDate).Days}};width=22} 

        

    if ($DBName)
 
            {
 
        dir -force $location | where-object {$_.Name -eq $DBName; $_.Refresh()} | 

        format-table $DisplayResults 

        }
 
    else   

            {
 
        dir -force $location | where-object {$_.Name -ne "tempdb"; $_.Refresh()} | 

        Format-table $DisplayResults 

        }         

}
#Get-DBBackupInfo localhost