PowerShell – SQL Databases Backup Status Report of Multiple Servers

 

This post is a requirement from one of my blog reader.

There are plenty of scripts available to get the backup status of the databases across multiple server. The  requirement is to read a server name and application names from an input file. The server name is used for building a connection and to get the required backup details but where as an app name is used for making an heading in a HTML body.

The Input CSV file consists of Servername, ApplicationName. In this case the App names are separated ‘/’.

InputFile

You need to change the below colored input values as per your environment setup

  • ServerList – Input file where DB Server and Application Names are Listed
  • OutputFile – Used for HTML Email body
  • emlist – You can send it more than one intended receipients. The receipients list are separated by comma
  • MailServer – Valid SMTP servername

CODE:

#Change value of following variables as needed
$ServerList = "f:\Powersql\ServerList.csv"
$OutputFile = "f:\Powersql\Output.htm"
$emlist="pjayaram@appvion.com,prashanth@abc.com"
$MailServer="maa.stmp.com"
 
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
  <TR>
   <TH><B>Database Name</B></TH>
   <TH><B>RecoveryModel</B></TD>
   <TH><B>Last Full Backup Date</B></TH>
   <TH><B>Last Differential Backup Date</B></TH>
   <TH><B>Last Log Backup Date</B></TH>
   </TR>"
  
  
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
Import-Csv $ServerList |ForEach-Object {
$ServerName=$_.ServerName
$AppName=$_.ApplicationName
$HTML += "<TR bgColor='#ccff66'><TD colspan=8 align=center>$ServerName - $AppName</TD></TR>"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
 Foreach($Database in $SQLServer.Databases)
{
$DaysSince = ((Get-Date) - $Database.LastBackupDate).Days
$DaysSinceDiff = ((Get-Date) - $Database.LastDifferentialBackupDate).Days
$DaysSinceLog = ((Get-Date) - $Database.LastLogBackupDate).Days
IF(($Database.Name) -ne 'tempdb' -and ($Database.Name) -ne 'model')
{
if ($Database.RecoveryModel -like "simple" )
{
if ($DaysSince -gt 1){
  $HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>NA</TD>
     </TR>"
}
}
  if ($Database.RecoveryModel -like "full" )
{
if ($DaysSince -gt 1){
  $HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>$($Database.LastLogBackupDate)</TD>
     </TR>"
}
}
if ($DaysSince -lt 1)
{
$HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='GREEN'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>$($Database.LastLogBackupDate)</TD>
     </TR>"
}
 }
}
}
 
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
 
Function sendEmail  
 
{ 
param($from,$to,$subject,$smtphost,$htmlFileName)  
 
$body = Get-Content $htmlFileName 
$body = New-Object System.Net.Mail.MailMessage $from, "$to", $subject, $body 
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
 
}  
$date = ( get-date ).ToString('yyyy/MM/dd')
$emlist
sendEmail pjayaram@appletonideas.com $emlist "Backup Report - $Date" $MailServer $OutputFile

 

Output

BackupStatusReport

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in Backup and Restore, PowerShell, SQL and tagged , . Bookmark the permalink.

13 Responses to PowerShell – SQL Databases Backup Status Report of Multiple Servers

  1. sanju says:

    code is not working its giving file can not open error.

    • Hi Sanju,

      Can you give me more information?

      Could you check the database is active and directory exists?

      Prashanth

      • sanju says:

        Hi,
        I am getting this kind of result.
        FullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSimpleSimpleSimpleFullSimpleFullSimpleFullFullSimpleSim

        Database Name

        RecoveryModel

        Last Full Backup Date

        Last Differential Backup Date

        Last Log Backup Date


        AdventureWorks 09/08/2016 00:08:02 01/01/0001 00:00:00 09/08/2016 14:00:12
        AdventureWorksDW2008 09/08/2016 00:07:30 01/01/0001 00:00:00 NA
        distribution 09/08/2016 00:07:56 01/01/0001 00:00:00 NA
        master 09/08/2016 00:05:03 01/01/0001 00:00:00 NA
        msdb 09/08/2016 00:07:14 01/01/0001 00:00:00 NA
        ReportServer 09/08/2016 00:07:28 01/01/0001 00:00:00 09/08/2016 14:00:11
        ReportServerTempDB 09/08/2016 00:07:29 01/01/0001 00:00:00 NA
        ssmatesterdb 09/08/2016 00:07:31 01/01/0001 00:00:00 09/08/2016 14:00:11
        sysdb 09/08/2016 00:07:31 01/01/0001 00:00:00 NA
        test 09/08/2016 00:07:30 01/01/0001 00:00:00 09/08/2016 14:00:11

        AdventureWorks 09/08/2016 00:08:02 01/01/0001 00:00:00 09/08/2016 14:00:12
        AdventureWorksDW2008 09/08/2016 00:07:30 01/01/0001 00:00:00 NA
        distribution 09/08/2016 00:07:56 01/01/0001 00:00:00 NA
        master 09/08/2016 00:05:03 01/01/0001 00:00:00 NA
        msdb 09/08/2016 00:07:14 01/01/0001 00:00:00 NA
        ReportServer 09/08/2016 00:07:28 01/01/0001 00:00:00 09/08/2016 14:00:11
        ReportServerTempDB 09/08/2016 00:07:29 01/01/0001 00:00:00 NA
        ssmatesterdb 09/08/2016 00:07:31 01/01/0001 00:00:00 09/08/2016 14:00:11
        sysdb 09/08/2016 00:07:31 01/01/0001 00:00:00 NA
        test 09/08/2016 00:07:30 01/01/0001 00:00:00 09/08/2016 14:00:11

        and there is nothing attached in mail.

        please help as this is very urgent and important.
        Regards,
        Sanju

      • Hi Sanju,

        Can you check the OutputFile?

        I think you might have some problem with email configuration.

        Can you test email configuration?

        Prashanth

      • sanju says:

        Get-Content : Cannot bind argument to parameter ‘Path’ because it is null.
        At line:4 char:20
        + $body = Get-Content <<<< $htmlFileName
        + CategoryInfo : InvalidData: (:) [Get-Content], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.GetContentCommand

        getting this error in last.
        only getting the test server data on which I am running this script.same data is repeating in all other rows.
        in email no data is there.

      • sanju says:

        this is the result of output file only. only test server data is reflecting again and again. I am getting the email but without any data.

      • Did you change the first few lines of the code?

        $ServerList =”f:\Powersql\ServerList.csv”
        $OutputFile = “f:\Powersql\Output.htm”

        Try to give a valid path and try to run the code.

  2. Att says:

    Instead of showing the “Null Date” where there is no backup information, can we use “N/A” or something?

    • Hi Att,

      Can you try the below code?

      Have validated for all the backup. Please check.

      #Change value of following variables as needed
      $ServerList = “f:\Powersql\ServerList2.csv”
      $OutputFile = “f:\Powersql\Output.htm”
      $emlist=”pjayaram@appvion.com, powershellsql@app.com
      $MailServer=”abc.smtp.com”

      $HTML = ‘
      #Header{font-family:”Trebuchet MS”, Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
      #Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
      #Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
      #Header tr.alt td {color:#000;background-color:#EAF2D3;}

      $HTML += ”

      Database Name
      RecoveryModel
      Last Full Backup Date
      Last Differential Backup Date
      Last Log Backup Date

      [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
      Import-Csv $ServerList |ForEach-Object {
      $ServerName=$_.ServerName
      $AppName=$_.ApplicationName
      $HTML += “$ServerName – $AppName”
      $SQLServer = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $ServerName
      Foreach($Database in $SQLServer.Databases)
      {
      if ($Database.LastBackupDate -eq “1/1/0001 12:00:00 AM”)
      {
      $DaysSince=’NA – Never Initiated Full Backup’
      }
      ELSE
      {
      $DaysSince = $Database.LastBackupDate
      }
      if ($Database.LastDifferentialBackupDate -eq “1/1/0001 12:00:00 AM” -OR $Database.LastDifferentialBackupDate -eq “1/1/0001 00:00:00”)
      {
      $DaysSinceDiff=’NA – Never Initiated Diff Backup’
      }
      ELSE
      {
      $DaysSinceDiff = $Database.LastDifferentialBackupDate
      }
      if ($Database.LastLogBackupDate -eq “1/1/0001 12:00:00 AM”)
      {

      $DaysSinceLog=’NA – Never Initiated Log Backup’
      }
      ELSE
      {
      $DaysSinceLog =$Database.LastLogBackupDate
      }
      IF(($Database.Name) -ne ‘tempdb’ -and ($Database.Name) -ne ‘model’)
      {
      if ($Database.RecoveryModel -like “simple” )
      {
      if (((Get-Date) – $Database.LastBackupDate).Days -gt 1){
      $HTML += ”
      $($Database.Name)
      $($Database.RecoveryModel)
      $DaysSince
      $DaysSinceDiff
      NA

      }
      }
      if ($Database.RecoveryModel -like “full” )
      {
      if (((Get-Date) – $Database.LastBackupDate).Days -gt 1){
      $HTML += ”
      $($Database.Name)
      $($Database.RecoveryModel)
      DaysSince
      $DaysSinceDiff
      $DaysSinceLog

      }
      }
      if (((Get-Date) – $Database.LastBackupDate).Days -lt 1)
      {
      $HTML += ”
      $($Database.Name)
      $($Database.RecoveryModel)
      $DaysSince
      $DaysSinceDiff
      $DaysSinceLog

      }
      }
      }
      }

      $HTML += “”
      $HTML | Out-File $OutputFile

      Function sendEmail

      {
      param($from,$to,$subject,$smtphost,$htmlFileName)

      $body = Get-Content $htmlFileName
      $body = New-Object System.Net.Mail.MailMessage $from, “$to”, $subject, $body
      $body.isBodyhtml = $true
      $smtpServer = $MailServer
      $smtp = new-object Net.Mail.SmtpClient($smtpServer)
      $smtp.Send($body)

      }
      $date = ( get-date ).ToString(‘yyyy/MM/dd’)
      $emlist
      sendEmail pjayaram@appletonideas.com $emlist “Backup Report – $Date” $MailServer $OutputFile

      –Prashanth

      • Atsung Imchen says:

        Hello Prashanth hope this thread is still alive, my apologies for the delay, I ran it and i get the following error. The output comes out only with HTML settings

        The following exception was thrown when trying to enumerate the collection: “Failed to connect to server ..”.
        At line:20 char:8
        + Foreach <<<< ($Database in $SQLServer.Databases)
        + CategoryInfo : NotSpecified: (:) [], ExtendedTypeSystemException
        + FullyQualifiedErrorId : ExceptionInGetEnumerator

        The servers are open to connect i have other Powershell scripts running just fine.

      • Hi,

        Do you have access to the server?

        Is it a named instance?

        Can you run the below two lines before running the code?

        [Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)
        [Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlEnum”)

        Prashanth

  3. Att says:

    Some are named and some are default.
    I ran the lines before the code and i still get the same error.

    The connection should not have any issues because as mentioned i have others scripts running remotely through powershell.

  4. Att says:

    This gives more or less the same result but without the “NA details” and the function to send the mail.

    $ServerList = Get-Content “D:\ServerList.txt”
    $OutputFile = “D:\Output.htm”

    $HTML = ‘
    #Header{font-family:”Trebuchet MS”, Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
    #Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
    #Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
    #Header tr.alt td {color:#000;background-color:#EAF2D3;}

    $HTML += ”

    Database Name
    RecoveryModel
    Last Full Backup Date
    Last Differential Backup Date
    Last Log Backup Date

    [System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null
    ForEach ($ServerName in $ServerList)
    {
    $HTML += “$ServerName

    $SQLServer = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $ServerName
    Foreach($Database in $SQLServer.Databases)
    {
    $HTML += ”
    $($Database.Name)
    $($Database.RecoveryModel)
    $($Database.LastBackupDate)
    $($Database.LastDifferentialBackupDate)
    $($Database.LastLogBackupDate)

    }
    }

    $HTML += “”
    $HTML | Out-File $OutputFile

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