Finding last backups with Powershell

Last weekend, I did a change control that was in the middle of when we normally do our full backups. As a result, some of the databases didn't get backed up via the normal process. Our data center guys gave me the option of just running the whole thing again, but that would have backed up already backed up databases and given that our retention policy is based on time (and not by number), the backup drive isn't sized to accommodate that. I was also without my script repository (since moved to Dropbox so that won't happen again), so I needed a quick way to determine which databases needed to be backed up. Enter powershell:

$instance = new-object Microsoft.SqlServer.Management.Smo.Server "server\instance"
$d = get-date
$instance.Databases | where {($d-$_.LastBackupDate).Days -gt 0} `
   | select Name, @{Name="Days since last backup";Expression={($d-$_.LastBackupDate).Days}}

Which I thought was pretty good given that I was in a hotel room in Colorado and I'd been up for almost 24 hours at that point. Powershell, you're my only friend. Lastly, you get what you pay for.

No comments:

Post a Comment