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!



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 + “‘);”