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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Function Get-LatestOlaBackup { param( $DBRootDir ) $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] @{ 'TimeStamp'=$NewestFile.LastWriteTime; 'Database'=$DBName.Name; 'BackupType'=$rm.Name } } } Function Get-LatestOlaBackups { param( [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:
|
1 |
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
|
1 |
Get-LatestOlaBackups -RootDir S:\SQLBackups\YourServerName | Select @{N='DayOnly';E={$_.TimeStamp.Date}} | Group DayOnly |
