Powershell Script for Backup Monitor for all the sqlservers in network

Posted: March 21, 2011 in Sqlserver

Problem

I had a scenario where one of my production sqlserver agent went into hung state due to which none of the sql jobs were running and no backups were happening though we have a NSM agent sitting on each of the production server which monitors the services and disk threshold. Since the Sql agent service was in running status and due to some reason it was not working as expected. Unless and until the service is down CA NSM agent will not trigger the event as an incident request to DBA.

Solution

I started to thing about something which monitor all my production server’s backup and send as an consolidated email for the backup status of all sqlserver’s.

I got a idea to develop something which is easy to maintain and identify the failure of backup by seeing the email.

Powershell script is a upgraded part of technet library.  I have rebuilded this script to record the output in excel sheet and send as an e-mail attachment to the DBA Team.

This scripts point you exactly which server and which database did not got backed up and from how many days backups are failing.

—————————————————————————————————————————————

### 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)

Advertisements
Comments
  1. Fabrizio Faleni says:

    It’s very nice and it works like a charm! Thanks for this nice script!
    Fabrizio Faleni

  2. NJ-DBA says:

    This is indeed very nice. Working on modifying it to accomodate multiple SLAs via dev/prod/stage serverlists.

    one peice of code that didnt quite work right for me one 2008 was:

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

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

    and now it works as intended

  3. Farouk says:

    I have just trie dthis script, all I get is the spreadsheet with the headers, no data, what am I doing wrong?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s