Monitor all you Sql Instances Backups from One Location

gistfile1.ps1

### Code that can be used to Monitor all you Sql Instances Backups from One Location

#Create a new Excel object using COM

$ErrorActionPreference = “silentlycontinue”

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $False

$Excel.DisplayAlerts = $false

$ExcelWorkbooks = $Excel.Workbooks.Add()

$Sheet = $ExcelWorkbooks.Worksheets.Item(1)

#$MonitorBody = “D:\PowerShell\PScripts\Mail.htm”

#$date = get-date -uformat “%Y%m%d”

$date = ( get-date ).ToString(‘yyyy/MM/dd’)

$save = “D:\PowerShell\ExcelReports\DatabaseBackup_Report.xls”

#Counter variable for rows

$intRow = 1

#Read the contents of the Servers.txt file

#foreach ($instance in get-content “serverlist.txt”)

 

##################Loop in all your sqlserver instances#########################

foreach ($instance in get-content “D:\PowerShell\PScripts\Backupserverlist.txt”)

{

#Create column headers

$Sheet.Cells.Item($intRow,1) = “INSTANCE NAME:”

$Sheet.Cells.Item($intRow,2) = $instance

$Sheet.Cells.Item($intRow,1).Font.Bold = $True

$Sheet.Cells.Item($intRow,2).Font.Bold = $True

 

$intRow++

 

$Sheet.Cells.Item($intRow,1) = “DATABASE NAME”

$Sheet.Cells.Item($intRow,2) = “LAST FULL BACKUP”

$Sheet.Cells.Item($intRow,3) = “LAST LOG BACKUP”

$Sheet.Cells.Item($intRow,4) = “FULL BACKUP AGE(DAYS)”

$Sheet.Cells.Item($intRow,5) = “LOG BACKUP AGE(HOURS)”

 

#Format the column headers

for ($col = 1; $col –le 5; $col++)

{

$Sheet.Cells.Item($intRow,$col).Font.Bold = $True

$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 50

$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 36

}

 

$intRow++

#######################################################

#This script gets SQL Server database information using PowerShell

 

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

 

# Create an SMO connection to the instance

$s = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $instance

 

$dbs = $s.Databases

#Formatting using Excel

ForEach ($db in $dbs)

{

if ($db.Name -ne “tempdb”) #We do not need the backup information for the tempdb database

{

#We use Date Math to extract the number of days since the last full backup

$NumDaysSinceLastFullBackup = ((Get-Date) – $db.LastBackupDate).Days

#Here we use TotalHours to extract the total number of hours

$NumDaysSinceLastLogBackup = ((Get-Date) – $db.LastLogBackupDate).TotalHours

if($db.LastBackupDate -eq “1/1/2005 12:00 AM”)

#This date is a start of Sqlserver infra.
#This is the default dateTime value for databases that have not had any backups
{

$fullBackupDate=”Never been backed up”
$fgColor3=”red”

}

else

{

$fullBackupDate=”{0:g}” -f $db.LastBackupDate
}

$Sheet.Cells.Item($intRow, 1) = $db.Name
$Sheet.Cells.Item($intRow, 2) = $fullBackupDate
$fgColor3=”green”

#Use the .ToString() Method to convert the value of the Recovery model to string and ignore Log #backups for databases with Simple recovery model

if ($db.RecoveryModel.Tostring() -eq “SIMPLE”)
{

$logBackupDate=”N/A”
$NumDaysSinceLastLogBackup=”N/A”

}
else

{

if($db.LastLogBackupDate -eq “1/1/2011 12:00 AM”)

{
$logBackupDate=”Never been backed up”
}

else

{
$logBackupDate= “{0:g2}” -f $db.LastLogBackupDate
}

}

$Sheet.Cells.Item($intRow, 3) = $logBackupDate

#Define your service-level agreement in terms of days here.

if ($NumDaysSinceLastFullBackup -gt 0)

{

$fgColor = 3

}

else

{

$fgColor = 50

}

$Sheet.Cells.Item($intRow, 4) = $NumDaysSinceLastFullBackup
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor
$Sheet.Cells.Item($intRow, 5) = $NumDaysSinceLastLogBackup
$intRow ++

}
}
$intRow ++
}

 
$Sheet.UsedRange.EntireColumn.AutoFit()
$ExcelWorkbooks.SaveAs($save)
$Excel.quit()
CLS

######Send Email with excel sheet as a attachment#######

$mail = New-Object System.Net.Mail.MailMessage
$att = new-object Net.Mail.Attachment($save)
$mail.From = “shamsul.hassan@gmail.com”
$mail.To.Add(“shamsul.hassan@gmail.com”)
$mail.Subject = “Database Backup Report of all Sqlserver for $date “
$mail.Body = “This mail gives us the detail information of all the database backup of production sqlserver which are schedule to run every day. Please review the Excel report enclosed with the mail every day and fix the failed backups which is marked in Red color and make sure the Full Backup Age(DAYS) is Zero days.Thanks – Md.S.Hassan”
$mail.Attachments.Add($att)
$smtp = New-Object System.Net.Mail.SmtpClient(“smtp.us.tesco.org”)
$smtp.Credentials = New-Object System.Net.NetworkCredential(“us\DBA_mon”,”xxxxx”)
$smtp.Send($mail)