Kyle Bracke

# SQL Server Nightly Health Check
# Checks backup recency, index fragmentation, and database size
# Emails formatted report to IT and management

$SQLServer  = "SQL-SERVER-01"
$Databases  = @("CompanyDB", "SAPB1DB", "ReportingDB")
$Recipients = @("it@company.com", "manager@company.com")

function Get-DatabaseHealth {
    param([string]$Server, [string[]]$Databases)

    $Report = @()
    foreach ($DB in $Databases) {
        $Query = @"
SELECT
    DB_NAME()                                           AS DatabaseName,
    SUM(size * 8 / 1024)                               AS SizeMB,
    (SELECT COUNT(*) FROM sys.indexes
     WHERE INDEXPROPERTY(object_id, name, 'IndexFragmentation') > 30)
                                                        AS FragmentedIndexes,
    (SELECT TOP 1 backup_finish_date
     FROM msdb.dbo.backupset
     WHERE database_name = DB_NAME()
     ORDER BY backup_finish_date DESC)                  AS LastBackup
FROM sys.database_files
"@
        $Result           = Invoke-Sqlcmd -ServerInstance $Server -Database $DB -Query $Query
        $HoursSinceBackup = ((Get-Date) - $Result.LastBackup).TotalHours

        $Report += [PSCustomObject]@{
            Database          = $DB
            SizeMB            = $Result.SizeMB
            FragmentedIndexes = $Result.FragmentedIndexes
            LastBackup        = $Result.LastBackup
            BackupStatus      = if ($HoursSinceBackup -lt 24) { "OK" } else { "STALE" }
        }
    }
    return $Report
}

$HealthData = Get-DatabaseHealth -Server $SQLServer -Databases $Databases
$HTMLReport = $HealthData | ConvertTo-Html -Title "SQL Server Health — $(Get-Date -Format 'yyyy-MM-dd')"

Send-MailMessage `
    -To        $Recipients `
    -Subject   "SQL Health Report: $(Get-Date -Format 'yyyy-MM-dd')" `
    -BodyAsHtml $HTMLReport `
    -SmtpServer "smtp.company.com"

# Azure Backup Job Verification
# Checks for failed/warned jobs in the past 48 hours and sends alerts
# Runs nightly via scheduled task

Import-Module Az.RecoveryServices

$VaultName     = "CompanyBackupVault"
$ResourceGroup = "Company-RG-Backup"

# Connect using managed identity in production environments
Connect-AzAccount -Identity

$Vault = Get-AzRecoveryServicesVault -Name $VaultName -ResourceGroupName $ResourceGroup
Set-AzRecoveryServicesVaultContext -Vault $Vault

# Pull jobs from the last 48 hours
$Jobs        = Get-AzRecoveryServicesBackupJob -From (Get-Date).AddHours(-48) -VaultId $Vault.ID
$FailedJobs  = $Jobs | Where-Object { $_.Status -eq "Failed" }
$WarningJobs = $Jobs | Where-Object { $_.Status -eq "CompletedWithWarnings" }
$SuccessJobs = $Jobs | Where-Object { $_.Status -eq "Completed" }

Write-Host "Backup Summary (last 48h):"
Write-Host "  Successful : $($SuccessJobs.Count)"
Write-Host "  Warnings   : $($WarningJobs.Count)"
Write-Host "  Failed     : $($FailedJobs.Count)"

if ($FailedJobs.Count -gt 0) {
    $AlertBody  = "BACKUP ALERT — $($FailedJobs.Count) job(s) failed.`n`n"
    $AlertBody += ($FailedJobs | Select-Object WorkloadName, Status, StartTime, ErrorDetails |
                   Format-Table -AutoSize | Out-String)

    Send-MailMessage `
        -To        "it@company.com" `
        -Subject   "ALERT: Azure Backup Failure — $(Get-Date -Format 'yyyy-MM-dd')" `
        -Body      $AlertBody `
        -SmtpServer "smtp.company.com"

    Write-Warning "Backup failures detected. Alert sent."
}

if ($WarningJobs.Count -gt 0) {
    Write-Warning "$($WarningJobs.Count) job(s) completed with warnings. Review in Azure portal."
}