WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Friday, January 27, 2012

"Trust but Verify" .... Your Backups.

Here is yet another script used in enterprise monitoring of SQL Server.  If you are in a big shop, you likely have a corporate backup solution in place, but what about development, test, etc?  You can use this script to checkout all the other servers in your environment to make sure they are getting a backup at least occasionally.

First up, include the SMO library and create a SQL SMO object.
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance

  This function takes an SMO object [$SmoSqlServer]
  The number of days before alerting the backup is old [$DaysToConsiderOld]
  Array of databases names to ignore.[$DBIgnoreList]
Function Get-DatabasesWithOldFullBackups([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer
, [int] $DaysToConsiderOld, [array]$DBIgnoreList)
if($DaysToConsiderOld -gt 0) {$DaysToConsiderOld *= -1;}
$bkupDate = (Get-Date).AddDays($DaysToConsiderOld);

$SmoSqlServer.Databases | Where-Object {$_.IsSystemObject -eq $false -and $_.ReadOnly -eq "False" -and $_.IsAccessible -eq "True" } `
| Where {$DBIgnoreList -notcontains $_.Name}`
| Where {($_.LastBackupDate -le $bkupDate)}`
| Select-Object Name,RecoveryModel,@{Label="LastFull";Expression={$_.LastBackupDate};}`
,@{Label="LastDiff";Expression={$_.LastDifferentialBackupDate};}, Size

Make sure you verify those backups!

No comments:

Post a Comment