question

Here you go! It could have been one liner but I decided to break it up for easier following. Let me know if you have any questions. Working with PoSH is a LOT of fun :)

    $BackupFolder = 'D:\Temp\Backup' #Paste the path to your backup folder b/w the single quotes
    #Let's get the files from the BACKUP folder
    $LatestBackup = Get-ChildItem -Path $BackupFolder | Where-Object {$_.Name -ilike "*.bak"} | Sort-Object -Descending LastWriteTime | Select-Object Name,LastWriteTime -First 1
    #Don't have to do it but I used it in the ELSE
    $LatestBackupName = $LatestBackup.Name
    
    if ($LatestBackupName -eq $null)
    {
     Write-Host "No BAK files were found in the path provided"
    }
    else
    {
     #If the backup LastWriteTime is less than today then the backup failed.
     if (($LatestBackup).LastWriteTime.ToString("M/dd/yyyy") -lt (Get-Date -Format d)
     {
      Write-Host "WARNING: There is no today's backup!"
     }
     else
     {
      Write-Host "The latest backup is $LatestBackupName"
     }
    }

I would actually take a bit of a different route based on what the end result or outcome is for this script. SQL Server is much better source at (1) determining when the last backup was done for a database, and (2) giving me the file path of that backup. One thing to consider is what if you end up with an application like Sharepoint that can take native backups of a database from the admin console. You can add your logic where you want for a date being older than current run time and if the backup file exist in a different directory, this is just code for the meat of getting the path initially.

So if I want to use PowerShell to get the last physical backup file and the test of that path is good:

    function Get-LatestBackup
    {
        [cmdletbinding()]
        param(
            [Parameter(
                Mandatory=$true,
                Position=0
                )]
            [ValidateNotNull()]
            [Alias("instance")]
            [string]$server
            )
    
        # For SQL Server 2012
        Import-Module SQLPS -DisableNameChecking -NoClobber | Out-Null
        # For SQL Server 2008 and 2008 R2, un-comment the next line
        #Add-PSSnapin SQL*
    
        $s = New-Object Microsoft.SqlServer.Management.Smo.Server $server
    
        $dbList = $s.Databases | where {$_.ID -ne 2} | select Name, LastBackupDate
    
        #check whether the last backup exist on the server
        foreach ($db in $dbList)
        {
            $currentDB = $db.Name
            [datetime]$lastBackupDate = $db.LastBackupDate
    
            $query = @"
    SELECT bmf.physical_device_name
    FROM sys.databases s
    INNER JOIN msdb.dbo.backupset bs ON (bs.database_name = s.name )
    LEFT OUTER JOIN msdb.dbo.backupmediaset bms ON ( bs.media_set_id = bms.media_set_id )
    LEFT OUTER JOIN msdb.dbo.backupmediafamily bmf ON ( bmf.media_set_id = bms.media_set_id )
    WHERE s.name = '$($currentDB)'
    AND bs.backup_finish_date = '$($lastBackupDate)'
    "@
            $backupPath = (Invoke-Sqlcmd -ServerInstance $s -Database msdb -Query $query -QueryTimeout ([int]::MaxValue)).physical_device_name
            if (Test-Path $backupPath)
            {
                #Backup Exist
                Write-Verbose "Backup path for current database [$currentDB] found to be: $backupPath"
            }
            else
            {
                #backup file does not exist
                Write-Verbose "Backup path for current database [$currentDB] not found: $backupPath"
    
                #do something, put your logic here for what action you want to take
            }
        }
    }
    

An example execution of this from my local machine: ![alt text][1]

[1]: /storage/attachments/2400-capture.jpg

@DenisT - Thanks a lot for providing the script .I edited the script a bit more to suit my environment . If anyone is looking for a script to check if a backup file exists and send an email if it doesn't then here is the script . $BackupFolder = 'D:\Backups' #Paste the path to your backup folder b/w the single quotes $Date = "{0:yyyy_MM_dd}" -f (get-date).AddDays(-1) #If you backup is taken the previous night and your job runs the next day morning this date factor will come in handy #if the backup files are stored in subfolders then you need this script to search the backup folders for specific .bak files ForEach ($LatestBackupPath in Get-ChildItem -Path $BackupFolder) { $LatestBackupName = Get-ChildItem $LatestBackupPath.Fullname|Where-Object {$_.Name -ilike "*.bak"} | Sort-Object -Descending LastWriteTime | Select-Object Name,LastWriteTime -First 1 $LatestBackupName = $LatestBackupName.Name foreach ( $LatestBackupFile in $LatestBackupName) { if ($LatestBackupFile -eq $null) { "No BAK files were found in the folder $LatestBackupPath " | Out-File c:\temp\output.txt -Append } else { #If the backup LastWriteTime is less than today then the backup failed. if ($LatestBackupFile -notmatch ($Date)) { "WARNING: There is no backup today in folder $LatestBackupPath !!!" | Out-File c:\temp\output.txt -Append } else { "The latest backup is $LatestBackupFile" | Out-File c:\temp\output.txt -Append } } } } $recipient = ”your email” $smtpserver = "smtpserver" $msg = new-object Net.Mail.MailMessage $smtp = new-object Net.Mail.SmtpClient($smtpServer) $msg.From = "noreply_email" $msg.To.Add($recipient) $msg.Subject = "SQLServer Backup Report" $msg.Body = "Please find the daily Backup Report" foreach ($file in gci "c:\temp\output.txt"){ $att = New-Object Net.Mail.Attachment($file.fullname) $msg.Attachments.Add($att) } $smtp.Send($msg) #get-content c:\temp\output1.txt | select-string 'WARNING' - use this code if you want to check for any warnings inside the file and then send an email