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 ‘/’.
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
code is not working its giving file can not open error.
LikeLike
Hi Sanju,
Can you give me more information?
Could you check the database is active and directory exists?
Prashanth
LikeLike
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
LikeLike
Hi Sanju,
Can you check the OutputFile?
I think you might have some problem with email configuration.
Can you test email configuration?
Prashanth
LikeLike
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.
LikeLike
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.
LikeLike
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.
LikeLike
Instead of showing the “Null Date” where there is no backup information, can we use “N/A” or something?
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike