SQL Server – Check When Last Backup Was Taken

dbcc-checkdbrestoresql server

Brent Ozar had an interesing newsletter on 2015-06-22; How do you manage dbas measuring backups, where he thinks that a good DBA should check when

  • the last backup was done
  • it was last restored
  • the last CheckDB was done

It turned out to be a good exercise for me. Not only did I find two databases that were not backed up, I also discovered that we do have databases where we have never checked that we can restore the backup.

So;
I have provided one solution to the task in the answer section. We have the convention that database names are unique and my script lists production server and test server(s), so I can compare them.
Do you have a solution that is better?

Best regards,

Henrik

Best Answer

I would actually use dbatools (https://dbatools.io) these days

Get-DBALastBackup -SQLinstance Instance

Which looks like this

enter image description here Old Answer Below

I am Powershell dude (sounds like I am attending a Powershell Anonymous meeting!!)

so this is the script I would use although I would use the objects returned to write to a database for a report or write to Excel or create an HTML email for my manager or whatever was required. Remember the $Servers should hold SERVERNAME\InstanceName,PORTNumber if any of those are not standard

# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$Servers = 
## A list 'Servername1','Servername2' a text file Get-Content 'PATHTOSERVERFILE' or query a database Invoke-SQLCmd -Server SERVERNAME -Database ALLMyInstances -Query "Select Name FROM Instances"
foreach($Server in $Servers)    
{
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Server
$lastDBCC_CHECKDB = @{Name="Last DBCC Check";Expression={$_.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | where {$_.Field.ToString() -eq "dbi_dbccLastKnownGood"} | Select Value -ExpandProperty Value}}
foreach($db in $srv.databases)
{
$db|Select Parent,Name,LastBackupDate,LastDifferentialBackupDate,LastLogBackupDate,$lastDBCC_CHECKDB
 }
}
Related Question