Get the last backup date for all databases using powershell

Quick script to get the last backup date for all databases (excluding tempdb & offline databases).  Marginally tested (works on my machine), so use at your own risk.

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$servers = Get-Content c:\Servers.txt

$outputs = @();

$servers | %{
	$srvName = $_
	$srvConn = New-Object "Microsoft.SqlServer.Management.Common.ServerConnection"
	$srvConn.ServerInstance = $srvName
	$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $srvConn

	$srv.Databases | where{$_.Name -ne 'tempdb' -and $_.Status -eq [Microsoft.SqlServer.Management.Smo.DatabaseStatus]::Normal} | %{

			$output = New-Object -TypeName PSObject -Property @{
			    Server = $srvName
			    Database = $_.Name
				LastBackupDate = $_.LastBackupDate
			}
			$outputs += $output
		}

}

$outputs | SELECT Server, Database, LastBackupDate 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: