#PSTip Retrieving SQL database last backup dates using SMO

Note: This tip requires PowerShell 2.0 or above.

For regular reporting and auditing purposes, it is always desired to capture the SQL last backup dates. SQL SMO database properties give us this information.

Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$smo = New-Object Microsoft.SqlServer.Management.Smo.Server $env:ComputerName
$smo.Databases | Select Name, LastBackupDate, LastLogBackupDate, LastDifferentialBackupDate

The above code gives information about all databases on the local SQL server. If you want to filter it down to a specific database, you can do that using the following command.

$smo.Databases["MyDB"] | Select LastBackupDate, LastLogBackupDate, LastDifferentialBackupDate

About the author: Ravikanth C

Ravikanth is the founder and editor of the PowerShell Magazine. He is also a PowerShell MVP who works at Dell Inc. He blogs at http://www.ravichaganti.com/blog and you can follow him on Twitter @ravikanth.

Related Posts