July 14, 2016 at 11:56 pm
$ServerList = Get-Content "D:\ServerList.txt"
$OutputFile = "D:\Output.htm"
$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
ForEach ($ServerName in $ServerList)
{
$HTML += "<TR bgColor='#ccff66'><TD colspan=5 align=center>$ServerName</TD></TR>"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
Foreach($Database in $SQLServer.Databases)
{
$HTML += "<TR>
<TD>$($Database.Name)</TD>
<TD>$($Database.RecoveryModel)</TD>
<TD>$($Database.LastBackupDate)</TD>
<TD>$($Database.LastDifferentialBackupDate)</TD>
<TD>$($Database.LastLogBackupDate)</TD>
</TR>"
}
}
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
July 15, 2016 at 9:05 am
What have you tried so far?
Hopefully send-mailmessage: https://technet.microsoft.com/en-us/library/hh849925.aspx
July 27, 2016 at 11:08 am
Here is the code that I use to get failed backups. You may be able to use it:
CLEAR
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# Initialize Arrays
$InstanceList = @()
$NoBackups = @()
# Don't check some databases, such as model and tempdb
$SkipDBs = "model","tempdb","pubs","northwind"
#Retrieve Instance list from database.
$ServerList = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "AdminSQLServerInstance"
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $ServerList.Databases.Item("AdminDatabase")
$ds = $db.ExecuteWithResults("SELECT [InstanceName] FROM [AdminDatabase].[dbo].[SQLServerInfo] WHERE AllowConnection = 1 AND CheckBackup = 1 ORDER BY InstanceName")
Foreach ($InstanceNameRow in (($ds.Tables[0]).Rows))
{
$InstanceList += $InstanceNameRow.Item(0)
}
# Loop through all instances
FOREACH ($InstanceName in $InstanceList)
{
Try
{
$Instance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName
# Calculate date\time, for date comparison.
# Calculated as 10 AM of the previous day. This is to allow for backups being taken before midnight.
#Get Date from Server where backup job is run.
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $Instance.Databases.Item("master")
$ds = $db.ExecuteWithResults("SELECT DATEADD(HH, -14, CAST(CONVERT(VARCHAR(30), CURRENT_TIMESTAMP, 101) AS DATETIME))")
$CompareDate = (($ds.Tables[0]).Rows[0]).Item(0)
#Retrieves the recovery model and the last backup dates for FULL, DIFFERENTIAL, and LOG backups.
$BackupList = $Instance.Databases | Where-Object {($SkipDBs -notcontains $_.Name)} | Where-Object {$_.Status.ToString().ToUpper() -eq "NORMAL"} | SELECT @{Name='InstanceName';Expression={$Instance.Name}}, @{Name='DatabaseName';Expression={$_.Name}}, RecoveryModel, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate, @{Name='Status';Expression={$_.Status}}
# Loop through each backup, and check for conditions.
ForEach ($Backup in $BackupList)
{
# See if a Full Backup has ever been taken.
IF ($Backup.LastBackupDate -eq "1/1/0001 12:00:00 AM")
{
$NoBackups += $Backup | SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, @{Name='Message';Expression={"Full Backup has never been taken"}}
}
ELSE
{
# Check to see if a Full backup is older than 1 week.
# A full backup may only be taken once a week, if differentials are being used.
IF (($Backup.LastBackupDate -lt $CompareDate.AddDays(-8)))
{
$NoBackups += $Backup | SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, @{Name='Message';Expression={"Full Backup is more than a week old"}}
}
ELSE
{
#Check to see if a Differential or a Full backup has been taken in the previous night.
IF (($Backup.LastBackupDate -lt $CompareDate) -and ($Backup.LastDifferentialBackupDate -lt $CompareDate))
{
$NoBackups += $Backup | SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, @{Name='Message';Expression={"Daily Full or Differential Backup is missing"}}
}
}
}
# Check Log Backup -
IF (($Backup.RecoveryModel.ToString().ToUpper() -ne "SIMPLE") -and ($Backup.LastLogBackupDate -lt $CompareDate))
{
$NoBackups += $Backup | SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, @{Name='Message';Expression={"Transaction Log Backup is missing"}}
}
}
}
catch
{
$BackupObj = "" | SELECT @{Name='InstanceName';Expression={$InstanceName}}, @{Name='DatabaseName';Expression={""}}, @{Name='LastBackupDate';Expression={"1/1/0001 12:00:00 AM"}}, @{Name='LastDifferentialBackupDate';Expression={"1/1/0001 12:00:00 AM"}}, @{Name='Message';Expression={"Failed to retrieve backup data from instance"}}
$NoBackups += $BackupObj
}
}
#$NoBackups
IF ($NoBackups.Count -gt 0)
{
$a = "<style>"
$a = $a + "<!-- "
$a = $a + " TD{font-family: Arial; font-size: 8pt;} "
$a = $a + "--->"
$a = $a + "BODY{background-color:peachpuff;}"
$a = $a + "TABLE{font-family: Arial;font-size: 8pt;width:100%; height:75%; border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:PaleGoldenrod}"
$a = $a + "</style>"
# Email version for SQL job
# Send Email listing databases that have no backups.
$smtpServer = "smtpServer.yourcompany.com"
$smtpFrom = "FromEmail@yourcompany.com"
$smtpTo = "ToEmail@yourcompany.com"
$messageSubject = "Backup Failures"
$message = New-Object System.Net.Mail.MailMessage $smtpfrom, $smtpto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true
$message.Body = $NoBackups | Select-Object @{Name='Instance Name';Expression={$_.InstanceName}}, @{Name='Database Name';Expression={$_.DatabaseName}}, @{Name='Last Backup';Expression={$_.LastBackupDate}}, @{Name='Last Differential Backup';Expression={$_.LastDifferentialBackupDate}}, Message | ConvertTo-HTML -head "<H2>Missing Backups</H2>" -body $a
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($message)
}
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply