Getting last backup date for Ola Hallegren SQL Maintenance Solution


As already mentioned in this post, Ola ‘s solution to backup your SQL Server databases is recognized as a easy-to-deploy, no stress, solution. You can easily build on top of it.

Without installing any SQL cmdlet nor looking in the table the scripts produce, here is a solution to get the latest backups for each database solely based on the files’ timestamps.

<pre class="lang:ps decode:true" title="Getting SQL Freshest backup time">Function Get-LatestOlaBackup {
$RecoveryModels = Get-ChildItem $DBRootDir -Attributes Directory
$DBName = Get-Item $DBRootDir
foreach ($rm in $RecoveryModels) {
Write-Verbose "      last $rm backup for $dbRootDir"
  $NewestFile = Get-ChildItem $DBRootDir | Sort-Object -Property LastWriteTime | Select-Object -First 1
  [PSCustomObject] @{


Function Get-LatestOlaBackups {
[string] $RootDir

$databases = Get-ChildItem $RootDir -Attributes Directory
foreach ($db in $databases) {
Write-Verbose "Getting last backup for $db"
Get-LatestOlaBackup -DBRootDir $db.FullName


The usage is simple:

<pre class="lang:ps decode:true " title="OLA: Use of Get-LatestOlaBackup">Get-LatestOlaBackups -RootDir S:\SQLBackups\YourServerName | Sort TimeStamp -Desc

More elaborated use is to create a report to group the databases by their last backup day

<pre class="lang:ps decode:true " title="OLA: Group SQL backups by date">Get-LatestOlaBackups -RootDir S:\SQLBackups\YourServerName | Select @{N='DayOnly';E={$_.TimeStamp.Date}} | Group DayOnly