Check the Last SQL Server Backup Date using Windows PowerShell

Problem
In a previous tip, Retrieve List of Databases and their Properties using PowerShell, you've seen how you can use Windows PowerShell to audit database properties and use Microsoft Excel to generate a report. Part of the daily SQL Server DBA tasks is to check the backups of all the databases in a SQL Server instance. How do we use Windows PowerShell to check for the last backup date of SQL Server databases and to confirm that they still meet our service level agreement?

Solution
One of the challenges I had when I was starting out as a SQL Server DBA was to check for the last backup date for a database on a daily basis to make sure we meet our service level agreements (SLA). One way to do this is to find out which tables in the msdb database contain the records of the backup history. What's really challenging here is the fact that you would have to look at the tables and their corresponding relationships to build the query joins. Plus, the msdb database will only contain records for databases with backups. What about those that don't? You would need to join tables in the msdb database with those in the master database

For SQL Server 2005/2008, the script below displays the last backup date of all the databases on your SQL Server instance. This script is taken from the MSDN Code Gallery

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

For SQL Server 2000, you can simply replace the sys.databases table with master.dbo.sysdatabases.

With Windows PowerShell, things are a bit easy as you have seen from the previous tips. The Database class represents a SQL Server database object. What we're more concerned about in this tip are the properties that specifically pertain to backups - LastBackupDate and LastLogBackupDate. The properties are self-explanatory. The only thing we've added from the scripts in the previous tips is the last line, adding new properties we've already mentioned, highlighting the power and simplicity of Windows PowerShell from the script.

[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

Notice that those databases that have not been backed up return a value of 1/1/0001 12:00:00AM.

Let's do some Date Math and add the call to the Excel object as we did in the previous tip to format the results. The script above is more than enough for what we need. We only use Excel and the formatting for reporting purposes.

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True

$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 ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls