Powershell:
CLS
# Continue even if there are errors
$ErrorActionPreference = "Continue";
# Set your warning and critical thresholds
$on = "GOOD";
$off = "CRITICAL";
$warn ="WARNING"
#Set colors for table cell backgrounds
$redColor = "#FF0000"
$orangeColor = "#FBB917"
$greenColor = "#01DF3A"
$serverList = Get-Content "C:\Source\Reports\Execution\InstanceList.txt"
$dbStatus = "C:\Source\Reports\BackupReport\Report\BackupReport.html";
If (Test-Path $dbStatus)
{
Remove-Item $dbStatus
}
# Create and write HTML Header of report
$titleDate = get-date -uformat "%d/%m/%Y"
$header = "
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>
<title>Backup Report</title>
<STYLE TYPE='text/css'>
<!--
td {
font-family: Calibri;
font-size: 12px;
border-top: 1px solid #636363;
border-right: 1px solid #636363;
border-bottom: 1px solid #636363;
border-left: 1px solid #636363;
padding-top: 0px;
padding-right: 0px;
padding-bottom: 0px;
padding-left: 0px;
}
body {
margin-left: 5px;
margin-top: 5px;
margin-right: 0px;
margin-bottom: 10px;
table {
border: thin solid #000000;
}
-->
</style>
</head>
<center>
<body>
<table width='95%'>
<tr bgcolor='#0B0B61'>
<td colspan='7' height='32' align='center'>
<font face='calibri' color='#FFFFFF' size='3'><strong>Production: Microsoft SQL Server: Database Backup Report: $titledate</strong></font>
</td>
</tr>
</table>
"
Add-Content $dbStatus $header
# Create and write Table header for report
$tableHeader = "
<table width='95%' style='font-weight:bold'><tbody>
<tr bgcolor=#CCCCCC>
<td width='15%' align='left'><font face='calibri' color='#003399'>Instance</font></td>
<td width='15%' align='left'><font face='calibri' color='#003399'>Database</font></td>
<td width='5%' align='center'><font face='calibri' color='#003399'>State</font></td>
<td width='5%' align='center'><font face='calibri' color='#003399'>Recovery</font></td>
<td width='10%' align='center'><font face='calibri' color='#003399'>Last Full Backup</font></td>
<td width='10%' align='center'><font face='calibri' color='#003399'>Last Diff Backup</font></td>
<td width='10%' align='center'><font face='calibri' color='#003399'>Last T-Log Backup</font></td>
<td width='5%' align='center'><font face='calibri' color='#003399'>Status</font></td>
<td width='10%' align='center'><font face='calibri' color='#003399'>Comment</font></td>
</tr>
"
Add-Content $dbStatus $tableHeader;
import-module sqlps -disablenamechecking
foreach($ser in $serverList)
{
Write-Host '[Fetching Backup Information]: ' $ser
$database = Invoke-Sqlcmd -ServerInstance $ser -Database master -Query "SELECT
@@SERVERNAME AS Instance
,name AS [Database_name]
,state_desc
,recovery_model_desc
,[D] AS last_full_backup
,[I] AS last_differential_backup
,[L] AS last_tlog_backup
,CASE
/* These conditions below will cause a CRITICAL status */
WHEN [D] IS NULL THEN 'CRITICAL' -- if last_full_backup is null then critical
WHEN [D] < DATEADD(DD,-1,CURRENT_TIMESTAMP) AND [I] IS NULL THEN 'CRITICAL' -- if last_full_backup is more than 2 days old and last_differential_backup is null then critical
WHEN [D] < DATEADD(DD,-7,CURRENT_TIMESTAMP) AND [I] < DATEADD(DD,-2,CURRENT_TIMESTAMP) THEN 'CRITICAL' -- if last_full_backup is more than 7 days old and last_differential_backup more than 2 days old then critical
WHEN recovery_model_desc <> 'SIMPLE' AND name <> 'model' AND [L] IS NULL THEN 'CRITICAL' -- if recovery_model_desc is SIMPLE and last_tlog_backup is null then critical
WHEN recovery_model_desc <> 'SIMPLE' AND name <> 'model' AND [L] < DATEADD(HH,-6,CURRENT_TIMESTAMP) THEN 'CRITICAL' -- if last_tlog_backup is more than 6 hours old then critical
--/* These conditions below will cause a WARNING status */
WHEN [D] < DATEADD(DD,-1,CURRENT_TIMESTAMP) AND [I] < DATEADD(DD,-1,CURRENT_TIMESTAMP) THEN 'WARNING' -- if last_full_backup is more than 1 day old and last_differential_backup is greater than 1 days old then warning
WHEN recovery_model_desc <> 'SIMPLE' AND name <> 'model' AND [L] < DATEADD(HH,-3,CURRENT_TIMESTAMP) THEN 'WARNING' -- if last_tlog_backup is more than 3 hours old then warning
/* Everything else will return a GOOD status */
ELSE 'GOOD'
END AS backup_status
,CASE
/* These conditions below will cause a CRITICAL status */
WHEN [D] IS NULL THEN 'No FULL backups' -- if last_full_backup is null then critical
WHEN [D] < DATEADD(DD,-1,CURRENT_TIMESTAMP) AND [I] IS NULL THEN 'FULL backup > 1 day; no DIFF backups' -- if last_full_backup is more than 2 days old and last_differential_backup is null then critical
WHEN [D] < DATEADD(DD,-7,CURRENT_TIMESTAMP) AND [I] < DATEADD(DD,-2,CURRENT_TIMESTAMP) THEN 'FULL backup > 7 day; DIFF backup > 2 days' -- if last_full_backup is more than 7 days old and last_differential_backup more than 2 days old then critical
WHEN recovery_model_desc <> 'SIMPLE' AND name <> 'model' AND [L] IS NULL THEN 'No LOG backups' -- if recovery_model_desc is SIMPLE and last_tlog_backup is null then critical
WHEN recovery_model_desc <> 'SIMPLE' AND name <> 'model' AND [L] < DATEADD(HH,-6,CURRENT_TIMESTAMP) THEN 'LOG backup > 6 hours' -- if last_tlog_backup is more than 6 hours old then critical
--/* These conditions below will cause a WARNING status */
WHEN [D] < DATEADD(DD,-1,CURRENT_TIMESTAMP) AND [I] < DATEADD(DD,-1,CURRENT_TIMESTAMP) THEN 'FULL backup > 7 day; DIFF backup > 1 day' -- if last_full_backup is more than 1 day old and last_differential_backup is greater than 1 days old then warning
WHEN recovery_model_desc <> 'SIMPLE' AND name <> 'model' AND [L] < DATEADD(HH,-3,CURRENT_TIMESTAMP) THEN 'LOG backup > 3 hours' -- if last_tlog_backup is more than 3 hours old then warning
/* Everything else will return a GOOD status */
ELSE 'No issues'
END AS status_desc
FROM (
SELECT
d.name
,d.state_desc
,d.recovery_model_desc
,bs.type
,MAX(bs.backup_finish_date) AS backup_finish_date
FROM master.sys.databases d
LEFT JOIN msdb.dbo.backupset bs ON d.name = bs.database_name
WHERE (bs.type IN ('D','I','L') OR bs.type IS NULL)
AND d.database_id not in (2) -- exclude tempdb
AND d.source_database_id IS NULL -- exclude snapshot databases
AND d.state NOT IN (1,6,10) -- exclude offline, restoring, or secondary databases
AND d.is_in_standby = 0 -- exclude log shipping secondary databases
GROUP BY d.name, d.recovery_model_desc,d.state_desc,bs.type
) AS SourceTable
PIVOT
(
MAX(backup_finish_date)
FOR type IN ([D],[I],[L])
) AS PivotTable
ORDER BY database_name;"
foreach($db in $database)
{
$server = $db.Instance;
$dbname = $db.Database_name;
$state =$db.state_desc;
$recovery = $db.recovery_model_desc;
$full=$db.last_full_backup;
$diff=$db.last_differential_backup;
$log=$db.last_tlog_backup;
$status = $db.backup_status;
# Set background color to white if just a warning
if($status -eq $on)
{
$color = $greenColor
}
# Set background color to Red if space is Critical
if($status -eq $off)
{
$color = $redColor
}
if($status -eq $warn)
{
$color = $orangeColor
}
$Comment = $db.status_desc;
$dataRow = "
<tr>
<td width='10%'align='left'>$Server</td>
<td width='10%'align='left'>$dbname</td>
<td width='5%'align='center'>$state</td>
<td width='5%' align='center'>$recovery</td>
<td width='10%' align='center'>$full</td>
<td width='5%' align='center'>$diff</td>
<td width='10%' align='center'>$log</td>
<td width='5%' bgcolor=`'$color`' align='center'>$status</td>
<td width='15%' align='center'>$Comment</td>
</tr>
"
Add-Content $dbStatus $dataRow;
Add-Content $dbStatus "</body></html>"
$i++
}
}
$messageParameters = @{
Subject = "Microsoft-SQL-Server-Fleet-Database-Backup-Report"
Body = "Find the attachment for Microsoft SQL Server Database Backup Report"
From = "ops-sql-notifier@xyz.com"
To = "adm-sql-notifier@xyz.com" #,"ops-sql-notifier@xyz.com"
# CC = ""
SmtpServer = "relay-smtp.x.xyz.com"
Attachment = "C:\Source\Reports\BackupReport\Report\BackupReport.html"
}
Send-MailMessage @messageParameters -BodyAsHtml
|
|
|
|
|
|