PowerShell – SQL Databases Backup Status Report of Multiple Servers

Posted on April 27, 2016 by Prashanth Jayaram

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

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/

View all posts by Prashanth Jayaram →

This entry was posted in Backup and Restore, PowerShell, SQL and tagged Backup status, Import-csv. Bookmark the permalink.

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

sanju says:

June 7, 2016 at 6:29 pm

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

Reply
- Prashanth Jayaram says:
  
  June 8, 2016 at 1:42 pm
  
  Hi Sanju,
  
  Can you give me more information?
  
  Could you check the database is active and directory exists?
  
  Prashanth
  
  Reply
  - sanju says:
    
    October 4, 2016 at 7:32 pm
    
    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
  - Prashanth Jayaram says:
    
    October 4, 2016 at 7:39 pm
    
    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:
    
    October 4, 2016 at 8:21 pm
    
    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:
    
    October 4, 2016 at 8:24 pm
    
    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.
  - Prashanth Jayaram says:
    
    October 5, 2016 at 7:48 pm
    
    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.
Att says:

July 8, 2016 at 11:29 am

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

Reply
- Prashanth Jayaram says:
  
  July 8, 2016 at 2:46 pm
  
  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
  
  Reply
  - Atsung Imchen says:
    
    October 13, 2016 at 3:12 pm
    
    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.
  - Prashanth Jayaram says:
    
    October 13, 2016 at 3:40 pm
    
    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
Att says:

October 13, 2016 at 4:08 pm

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.

Reply
Att says:

October 13, 2016 at 4:22 pm

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

Reply