All about databases..: Automations

Backup Check

How to automate your sql server daily backup check as a report.?

Below is a powershell script whereas it is been coded based on the general SQL backup strategy like weekly full daily differential and hourly log backup.

Code below:

# Continue even if there are errors
$ErrorActionPreference = "Continue";

# EMAIL PROPERTIES
$SMTPServer = "smtp-relay.xyz.im"
$Username = "xyz@gmail.com"
$to = "mvkally@gmail.com"
$subject = "Backup & Database Daily Health Check Report"

# REPORT PROPERTIES
# Path to the report
$reportPath = "C:\Backupcheck\report\";

# Report name
$reportName = "BackupReport_$(get-date -format ddMMyyyy).html";

# Path and Report name together
$BackupReport = $reportPath + $reportName

#Set colors for table cell backgrounds
$redColor = "#e50000"
$greenColor ="#00b200"
$greyColor = "#D3D3D3"
$whiteColor = "#FFFFFF"

# Get computer list to check backup
$computers = Get-Content "C:\Backupcheck\serverlist.txt";
$datetime = Get-Date -Format "MM-dd-yyyy_HHmmss";

# Remove the report if it has already been run today so it does not append to the existing report
If (Test-Path $Backupreport)
{
Remove-Item $Backupreport
}

# Cleanup old files older than 7 days..
$Daysback = "-7"
$CurrentDate = Get-Date;
$DateToDelete = $CurrentDate.AddDays($Daysback);
Get-ChildItem $reportPath | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item;

# Create and write HTML Header of report
$titleDate = get-date -uformat "%d-%m-%Y - %A"
$header = "
<html>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>
<title>DiskSpace Report</title>
<STYLE TYPE='text/css'>

</style>
</head>
<body>
<table width='100%'>
<tr bgcolor='#548DD4'>
<td colspan='7' height='30' align='center'>
<font face='calibri' color='#003399' size='4'><strong>Backup & Database Daily Health Check Report for $titledate</strong></font>
</td>
</tr>
</table>
"
Add-Content $Backupreport $header

# Create and write Table header for report
$tableHeader = "
<table width='100%'><tbody>
<tr bgcolor=#548DD4>
<td width='45%' align='center' >Database Name</td>
<td width='15%' align='center' >Last Full Backup Date</td>
<td width='15%' align='center' >Last Diff. Backup Date</td>
<td width='15%' align='center'>Last Log Backup Date</td>
<td width='10%' align='center' >Database Status</td>

</tr>
"
Add-Content $Backupreport $tableHeader

# Start processing backup check

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
foreach($computer in $computers)
{

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $computer
$dbs=$s.Databases

   $dataHeader = "
<tr>
   <td width='10%' colspan='5' bgcolor=`'$greyColor`' align='center' width=100%>Instance name: $computer</td>
</tr>"

Add-Content $Backupreport $dataHeader

# Check for each database
foreach($db in $dbs)

{
if ($db.IsSystemObject -eq $False)
{
$colorfullbk = $greenColor;
$colordiffbk = $greenColor;
$colorlogbk = $greenColor;
$colorstatus = $greenColor;
$today = (Get-Date).DayOfWeek;
$databasename =$db.Name;
[Date]$Fullbackupdt =$db.LastBackupDate;
[Date]$Diffbackupdt =$db.LastDifferentialBackupDate;
[Date]$Logbackupdt = "DB in simple recovery";
$databasestatus=$db.Status;
if ($db.Status -ne 'Normal')
{
$colorstatus=$redColor
}
[float]$databasesize=$db.Size;
#$databasesizeGB = [Math]::Round($databasesize / 1073741824, 2);
if ($db.RecoveryModel -ne 'SIMPLE')
{
[Date]$Logbackupdt =$db.LastLogBackupDate;
if( $today = 'Monday')
{
if($Logbackupdt -lt $CurrentDate.AddDays(-3) )
{
   $colorlogbk = $redColor
   }
}
elseif($Logbackupdt -lt $CurrentDate.AddDays(-1) )
{
   $colorlogbk = $redColor
   }
else
{
   $colorlogbk = $greenColor
   }

}

if( $today = 'Monday')
{
if($Fullbackupdt -lt $CurrentDate.AddDays(-3) )
{
   $colorfullbk = $redColor
   }
}
elseif($Fullbackupdt -lt $CurrentDate.AddDays(-8) )
{
   $colorfullbk = $redColor
   }
else
{
   $colorfullbk = $greenColor
   }

if( $today = 'Monday')
{
if($Diffbackupdt -lt $CurrentDate.AddDays(-4) )
{
   $colordiffbk = $redColor
   }
}
elseif($Diffbackupdt -lt $CurrentDate.AddDays(-1) )
{
   $colordiffbk = $redColor
   }
else
{
   $colordiffbk = $greenColor
   }



# Create table data rows
   $dataRow = "
   <tr>
   <td width='15%' >$databasename</td>
   <td width='10%' bgcolor=`'$colorfullbk`' >$Fullbackupdt</td>
<td width='10%' bgcolor=`'$colordiffbk`' >$Diffbackupdt</td>
   <td width='10%' bgcolor=`'$colorlogbk`' >$Logbackupdt</td>
<td width='10%' bgcolor=`'$colorstatus`' >$databasestatus</td>

   </tr>
"
Add-Content $Backupreport $dataRow;
}

   $i++
   #}
}

}
# Create table at end of report showing legend of colors for the critical and warning
$tableDescription = "
</table><br>
<table width='20%'>
<tr bgcolor='White'>
<td width='5%' align='center' bgcolor='#FF0000'> Hurray!!! Success :-) </td>
<td width='5%' align='center' bgcolor='#e50000'> Error??? Something to look into :-( </td>
</tr>
"
Add-Content $Backupreport $tableDescription
Add-Content $Backupreport "</body></html>"
$message = New-Object System.Net.Mail.MailMessage
$message.subject = $subject
$message.IsBodyHtml = $true
$message.body = get-content $Backupreport
$message.to.add($to)
$message.from = $username

$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer);

#$smtp.send($message)

write-output “Email Sent!!”

--------------------------------------------------------------------------------------------------------------------------

Drive Space Check (HDD / SSD drives)

# Continue even if there are errors

$ErrorActionPreference = "Continue";

# Set your warning and critical thresholds

$percentWarning = 25;

$percentCritcal = 15;

# EMAIL PROPERTIES

$SMTPServer = "smtp-relay.abc.in"

$Username = "xyz@gmail.com"

$to = "mvkally@gmail.com"

$subject = "Disk Space Monitoring Report"

# REPORT PROPERTIES

# Path to the report

$reportPath = "C:\ps1\report\";

# Report name

$reportName = "DiskSpaceRpt_$(get-date -format ddMMyyyy).html";

# Path and Report name together

$diskReport = $reportPath + $reportName

#Set colors for table cell backgrounds

$redColor = "#FF0000"

$orangeColor = "#FBB917"

$whiteColor = "#FFFFFF"

# Count if any computers have low disk space. Do not send report if less than 1.

$i = 0;

# Get computer list to check disk space

$computers = Get-Content "C:\ps1\serverlist.txt";

$datetime = Get-Date -Format "MM-dd-yyyy_HHmmss";

# Remove the report if it has already been run today so it does not append to the existing report

If (Test-Path $diskReport)

{

Remove-Item $diskReport

}

# Cleanup old files..

$Daysback = "-1"

$CurrentDate = Get-Date;

$DateToDelete = $CurrentDate.AddDays($Daysback);

Get-ChildItem $reportPath | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item;

# Create and write HTML Header of report

$titleDate = get-date -uformat "%m-%d-%Y - %A"

$header = "

<html>

<head>

<title>DiskSpace Report</title>

</style>

</head>

<body>

<font face='calibri' color='#003399' size='4'><strong>Daily Morning Report for $titledate</strong></font>

</td>

</tr>

</table>

Add-Content $diskReport $header

# Create and write Table header for report

$tableHeader = "

<td width='10%' align='center'>Server</td>

<td width='5%' align='center'>Drive</td>

<td width='15%' align='center'>Drive Label</td>

<td width='10%' align='center'>Total Capacity(GB)</td>

<td width='10%' align='center'>Used Capacity(GB)</td>

<td width='10%' align='center'>Free Space(GB)</td>

<td width='15%' align='center'>Freespace %</td>

<!-- <td width='5%' align='center'>RAM %</td>

</tr>

Add-Content $diskReport $tableHeader

# Start processing disk space

foreach($computer in $computers)

{

$disks = Get-WmiObject -ComputerName $computer -Class Win32_LogicalDisk -Filter "DriveType = 3"

$computer = $computer.toupper()

foreach($disk in $disks)

{

$deviceID = $disk.DeviceID;

$volName = $disk.VolumeName;

[float]$size = $disk.Size;

[float]$freespace = $disk.FreeSpace;

$percentFree = [Math]::Round(($freespace / $size) * 100);

$sizeGB = [Math]::Round($size / 1073741824, 2);

$freeSpaceGB = [Math]::Round($freespace / 1073741824, 2);

# Set background color to Orange if just a warning

if($percentFree -lt $percentWarning)

{

$color = $orangeColor

# Set background color to Orange if space is Critical

if($percentFree -lt $percentCritcal)

{

$color = $redColor

}

# Create table data rows

$dataRow = "

<tr>

<td width='10%'>$computer</td>

<td width='5%' align='center'>$deviceID</td>

<td width='10%' >$volName</td>

<td width='10%' align='center'>$sizeGB</td>

<td width='10%' align='center'>$usedSpaceGB</td>

<td width='10%' align='center'>$freeSpaceGB</td>

<td width='15%' bgcolor=`'$color`' align='center'>$percentFree</td>

<!-- <td width='5%' align='center'>$RAMpercent</td>

<td width='5%' align='center'>$CPUpercent</td> -->

</tr>

Add-Content $diskReport $dataRow;

Write-Host -ForegroundColor DarkYellow "$computer $deviceID percentage free space = $percentFree";

$i++

}

# Create table at end of report showing legend of colors for the critical and warning

$tableDescription = "

</table><br><table width='20%'>

<td width='10%' align='center' bgcolor='#FBB917'>Warning less than $percentWarning free space</td>

<td width='10%' align='center' bgcolor='#FF0000'>Critical less than $percentCritcal free space</td>

</tr>

Add-Content $diskReport $tableDescription

Add-Content $diskReport "</body></html>"

$message = New-Object System.Net.Mail.MailMessage

$message.subject = $subject

$message.IsBodyHtml = $true

$message.body = get-content $diskReport

$message.to.add($to)

$message.from = $username

$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer);

$smtp.send($message)

write-output “Email Sent!!”

----------------------------------------------------------------------------------------------------------------------------

Drive Space Check ( for mount drives)

# Continue even if there are errors

$ErrorActionPreference = "Continue";

# Set your warning and critical thresholds

$percentWarning = 25;

$percentCritcal = 10;

# EMAIL PROPERTIES

$SMTPServer = "smtp-relay.abc.in"

$Username = "xyz@gmail.com"

$to = "mvkally@gmail.com"

$subject = "Disk Space Monitoring Report"

# REPORT PROPERTIES

# Path to the report

$reportPath = "C:\Diskspace\report\";

# Report name

$reportName = "DiskSpaceRpt_$(get-date -format ddMMyyyy).html";

# Path and Report name together

$diskReport = $reportPath + $reportName

#Set colors for table cell backgrounds

$redColor = "#FF0000"

$orangeColor = "#FBB917"

$whiteColor = "#FFFFFF"

# Count if any computers have low disk space. Do not send report if less than 1.

$i = 0;

# Get computer list to check disk space

$computers = Get-Content "C:\Diskspace\serverlist.txt";

$datetime = Get-Date -Format "MM-dd-yyyy_HHmmss";

# Remove the report if it has already been run today so it does not append to the existing report

If (Test-Path $diskReport)

{

Remove-Item $diskReport

}

# Cleanup old files..

$Daysback = "-1"

$CurrentDate = Get-Date;

$DateToDelete = $CurrentDate.AddDays($Daysback);

Get-ChildItem $reportPath | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item;

# Create and write HTML Header of report

$titleDate = get-date -uformat "%m-%d-%Y - %A"

$header = "

<html>

<head>

<title>DiskSpace Report</title>

</style>

</head>

<body>

<font face='calibri' color='#003399' size='4'><strong>Disk space report(WK-GS prod) for $titledate</strong></font>

</td>

</tr>

</table>

Add-Content $diskReport $header

# Create and write Table header for report

$tableHeader = "

<td width='10%' align='center'>Server</td>

<td width='5%' align='center'>Drive</td>

<td width='15%' align='center'>Drive Label</td>

<td width='10%' align='center'>Total Capacity(GB)</td>

<td width='10%' align='center'>Used Capacity(GB)</td>

<td width='10%' align='center'>Free Space(GB)</td>

<td width='15%' align='center'>Freespace %</td>

</tr>

Add-Content $diskReport $tableHeader

# Start processing disk space

foreach($computer in $computers)

{

$disks = Get-WmiObject -ComputerName $computer -Class Win32_Volume -Filter "DriveType = 3"

$computer = $computer.toupper()

foreach($disk in $disks)

{

$deviceID = $disk.Name;

$volName = $disk.Label;

[float]$size = $disk.Capacity;

[float]$freespace = $disk.FreeSpace;

$percentFree = [Math]::Round(($freespace / $size) * 100);

$sizeGB = [Math]::Round($size / 1073741824, 2);

$freeSpaceGB = [Math]::Round($freespace / 1073741824, 2);

$usedSpaceGB = $sizeGB - $freeSpaceGB;

$color = $whiteColor;

# Set background color to Orange if just a warning

if($percentFree -lt $percentWarning)

{

$color = $orangeColor

# Set background color to Orange if space is Critical

if($percentFree -lt $percentCritcal)

{

$color = $redColor

}

# Create table data rows

$dataRow = "

<tr>

<td width='10%'>$computer</td>

<td width='5%' align='center'>$deviceID</td>

<td width='10%' >$volName</td>

<td width='10%' align='center'>$sizeGB</td>

<td width='10%' align='center'>$usedSpaceGB</td>

<td width='10%' align='center'>$freeSpaceGB</td>

<td width='15%' bgcolor=`'$color`' align='center'>$percentFree</td>

<!-- <td width='5%' align='center'>$RAMpercent</td>

<td width='5%' align='center'>$CPUpercent</td> -->

</tr>

Add-Content $diskReport $dataRow;

Write-Host -ForegroundColor DarkYellow "$computer $deviceID percentage free space = $percentFree";

$i++

}

# Create table at end of report showing legend of colors for the critical and warning

$tableDescription = "

</table><br><table width='20%'>

<td width='10%' align='center' bgcolor='#FBB917'>Warning less than $percentWarning free space</td>

<td width='10%' align='center' bgcolor='#FF0000'>Critical less than $percentCritcal free space</td>

</tr>

Add-Content $diskReport $tableDescription

Add-Content $diskReport "</body></html>"

$message = New-Object System.Net.Mail.MailMessage

$message.subject = $subject

$message.IsBodyHtml = $true

$message.body = get-content $diskReport

$message.to.add($to)

$message.from = $username

$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer);

$smtp.send($message)

write-output “Email Sent!!”

All about databases..

Pages

Automations

No comments:

Post a Comment

Translate