Check the Last SQL Server Backup Date using Windows PowerShell

SELECT
T1.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(T2.backup_finish_date), 101),‘Not Yet Taken’) AS LastBackUpTaken
FROM master.sys.databases T1 LEFT OUTER JOIN msdb.dbo.backupset T2
ON T2.database_name = T1.name
GROUP BY T1.Name
ORDER BY T1.Name

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “LOCALHOST\SQL2000”

$dbs=$s.Databases

#Retrieves the last backup dates – both for FULL and LOG backups
$dbs | SELECT Name,LastBackupDate, LastLogBackupDate | Format-Table -autosize

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
foreach ($instance in get-content “D:\SQL_Servers.txt”)
{

     #Create column headers
     $Sheet.Cells.Item($intRow,1) = “INSTANCE NAME:”
     $Sheet.Cells.Item($intRow,2) = $instance
     $Sheet.Cells.Item($intRow,1).Font.Bold = $True
     $Sheet.Cells.Item($intRow,2).Font.Bold = $True

     $intRow++

      $Sheet.Cells.Item($intRow,1) = “DATABASE NAME”
      $Sheet.Cells.Item($intRow,2) = “LAST FULL BACKUP”
      $Sheet.Cells.Item($intRow,3) = “LAST LOG BACKUP”
      $Sheet.Cells.Item($intRow,4) = “FULL BACKUP AGE(DAYS)”
      $Sheet.Cells.Item($intRow,5) = “LOG BACKUP AGE(HOURS)”

     #Format the column headers
     for ($col = 1; $col -le 5; $col++)
     {
          $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
          $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
          $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
     }

     $intRow++
      #######################################################
     #This script gets SQL Server database information using PowerShell

     [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

     # Create an SMO connection to the instance
     $s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance

     $dbs = $s.Databases

     #Formatting using Excel

ForEach ($db in $dbs) 
{
   if ($db.Name –ne “tempdb”) #We do not need the backup information for the tempdb database
   {
       $NumDaysSinceLastFullBackup = ((Get–Date) – $db.LastBackupDate).Days #We use Date Math to extract the number of days since the last full backup
       $NumDaysSinceLastLogBackup = ((Get–Date) – $db.LastLogBackupDate).TotalHours #Here we use TotalHours to extract the total number of hours
       
       if($db.LastBackupDate –eq “1/1/0001 12:00 AM”) #This is the default dateTime value for databases that have not had any backups
       {
           $fullBackupDate=“Never been backed up”
           $fgColor3=“red”
       }
       else
       {
           $fullBackupDate=“{0:g}” –f  $db.LastBackupDate
       }
   
       $Sheet.Cells.Item($intRow, 1) = $db.Name
       $Sheet.Cells.Item($intRow, 2) = $fullBackupDate 
   
       #We use the .ToString() Method to convert the value of the Recovery model to string and ignore Log backups for databases with Simple recovery model
       if ($db.RecoveryModel.Tostring() –eq “SIMPLE”)
       {
           $logBackupDate=“N/A”
           $NumDaysSinceLastLogBackup=“N/A”
       }
       else
       {
           if($db.LastLogBackupDate –eq “1/1/0001 12:00 AM”) 
           {
               $logBackupDate=“Never been backed up”
           }
           else
           {
               $logBackupDate= “{0:g2}” –f $db.LastLogBackupDate
           }
           
       }
   
       $Sheet.Cells.Item($intRow, 3) = $logBackupDate
   
       #Define your service-level agreement in terms of days here
       if ($NumDaysSinceLastFullBackup –gt 1)
       {
           $fgColor = 3
       }
       else
       {
           $fgColor = 0
       }
   
       $Sheet.Cells.Item($intRow, 4) = $NumDaysSinceLastFullBackup
       $Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
   
       $Sheet.Cells.Item($intRow, 5) =  $NumDaysSinceLastLogBackup
   
           
       $intRow ++
   
       }
   }
   $intRow ++


}