Powershell By Example: SQL Backups
I was asked a question at my IndyPASS presentation and a subsequent followup about using SMO objects with Powershell in order to say….
Get a list of the databases whom have not been backed up in the last 2 days and then import the results into a logging table. So in order to help everyone out here is a working example I whipped up fairly quickly to demonstrate that ability. Enjoy! 
Cheers,
AJ
Function GetBackupInfo([string] $SQLServer)
{
$sql= New-Object(‘Microsoft.sqlserver.management.smo.server’)$SQLServer
$resultset = $sql.Databases|Select-Object @{Name=‘server’;Expression={$sql.name}},name,lastbackupdate| where {$_.lastbackupdate -lt (get-date).AddDays(-2)}
#Now we create a database object for the logging db to put our results into
$sqlarchive = New-Object(‘Microsoft.sqlserver.management.smo.Database’)(“LPT-AJONES\SQL08”,“TestArchive”)
foreach($result in $resultset)
{
$sqlcmd = “INSERT INTO BADBACKUPLOG(name,lastbackupdate) VALUES(‘” + $result.name + “‘,'” + $result.lastbackupdate + “‘);”
$sqlarchive.ExecuteNonQuery($sqlcmd)
}
}