老版本SQL Server巡检报错？新增两行代码秒修复，省时攻略再升级！-优快云博客

文章来源于老苏畅谈运维，欢迎您转载，转载声明来源老苏畅谈运维

本文链接：https://blog.youkuaiyun.com/zhirongsu/article/details/146341080

再上一篇文章一分钟搞定SQL Server数据库巡检报告，省时又省力！，有读者反馈说执行报错，报错内容如下：“Invoke-Sqlcmd项识别为cmdlet、函数、脚本文件或可运行程序的名称。请检查名称的拼写,如果包括路径，请确保路径正确，然后再试一次”。在早期的SQLServer（如2008）可能存在这个问题，为了兼容PowerShell，需要在运行命令 invoke-sqlcmd 前执行如下语句：
Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

因此，如果出现上述问题，可将脚本修改如下：

<#
.SYNOPSIS
生成 SQL Server 巡检报告的脚本
.DESCRIPTION
该脚本通过执行多个 SQL 查询收集服务器信息，并生成格式化的 HTML 报告
#>
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
# 配置参数
$config = @{
    ReportDir      = "C:\check\"  # 修改为报告保存目录
    SqlServer      = "127.0.0.1"
    SqlDatabase    = "master"
    SqlScriptPath  = "C:\check\sqlquery\"  # 修改为sql脚本目录
    EventLogLimit  = 500
}

# 动态生成文件名
$ipAddress = (Get-NetIPAddress -AddressFamily IPv4 | Where-Object {
    $_.InterfaceAlias -notlike '*Loopback*' -and $_.PrefixOrigin -ne 'WellKnown'
} | Select-Object -First 1).IPAddress

$hostName = hostname
$dateString = Get-Date -Format "yyyyMMdd"
$config['ReportPath'] = Join-Path $config.ReportDir ("{0}-{1}-{2}.html" -f $ipAddress, $hostName.ToLower(), $dateString)

# 确保目录存在
if (-not (Test-Path $config.ReportDir)) {
    New-Item -ItemType Directory -Path $config.ReportDir -Force | Out-Null
}




# HTML 样式表
$htmlHeader = @"
<style>
    body { font-family: Segoe UI, Arial; margin: 25px; }
    h1 { color: #2c3e50; border-bottom: 2px solid #3498db; }
    h3 { color: #7f8c8d; }
    h4 { color: #2c3e50; margin-top: 30px; }
    table { border-collapse: collapse; width: 100%; margin: 20px 0; }
    th { background-color: #3498db; color: white; padding: 12px; text-align: left; }
    td { padding: 10px; border: 1px solid #ddd; }
    tr:nth-child(even) { background-color: #f8f9fa; }
</style>
"@

# 初始化报告内容
$reportSections = @()

# 公共函数：执行 SQL 查询并返回格式化表格
function Get-SqlResult {
    param(
        [string]$scriptName,
        [string[]]$properties,
        [string]$sectionTitle
    )

    $result = try {
        $queryPath = Join-Path $config.SqlScriptPath $scriptName
        Invoke-Sqlcmd -ServerInstance $config.SqlServer -Database $config.SqlDatabase -InputFile $queryPath -ErrorAction Stop
    }
    catch {
        return "<p style='color: red'>错误: $_</p>"
    }

    if (-not $result) { return "<p>没有找到相关数据</p>" }

    $htmlTable = $result | Select-Object $properties | ConvertTo-Html -Fragment
    return @"
<h4>$sectionTitle</h4>
$htmlTable
"@
}

# 创建报告头部
$reportHeader = @"
<!DOCTYPE html>
<html>
<head>
    <title>SQL Server数据库巡检报告</title>
    $htmlHeader
</head>
<body>
    <h1>SQL Server数据库巡检报告</h1>
    <h3>生成时间: $(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')</h3>
"@

# 处理检查项
$checkItems = @(
    @{ Script = 'version.sql';          Props = @('productversion','sp_level','edition');        Title = '数据库版本信息' },
    @{ Script = 'session.sql';          Props = @('login_time','host_name','login_name','program_name','host_process_id','cpu_time','total_elapsed_time');        Title = '当前数据库会话' },
    @{ Script = 'cpu.sql';              Props = @('wait_type','wait_time_s','pct','running_pct');        Title = '检查CPU是否有压力' },
    @{ Script = 'Memory.sql';           Props = @('Physical Memory (MB)','Available Memory (MB)','System Memory State'); Title = '检查服务器物理内存使用情况' },
    @{ Script = 'buffer_hit_ratio.sql'; Props = @('object_name','counter_name','cntr_value');       Title = '检查buffer cache hit ratio值' },
    @{ Script = 'page_life.sql';        Props = @('Server Name','object_name','Page Life Expectancy'); Title = '检查page life expectancy' },
    @{ Script = 'lazy_write.sql';       Props = @('counter_name','cntr_value');                       Title = '检查lazy write/sec' },
    @{ Script = 'disk.sql';             Props = @('Driveletter','Total_Space_GB','Free_Space_GB');     Title = '磁盘空间使用情况' },
    @{ Script = 'DB_information.sql';   Props = @('Database Name','file_id','name','Physical_name','Growth in MB','Max_size in MB','Total Size in MB'); Title = '检查数据库文件基本信息' },
    @{ Script = 'recovery_model.sql';   Props = @('Database Name','Recovery Model','Log Reuse Wait Description','Log Size (MB)','Log Used (MB)','Log Used %'); Title = '检查日志文件属性和恢复模式' },
    @{ Script = 'port.sql';             Props = @('IP_Address','port','state_desc');                  Title = '检查1433端口监听状态' },
    @{ Script = 'userinfo.sql';         Props = @('LoginName','PasswordLastSetTime','DaysUntilExpiration','PasswordExpirationDate','PasswordExpireChecked'); Title = '检查密码快过期的SQL账号' },
    @{ Script = 'job.sql';              Props = @('Job ID', 'Job Name','Exe Time','Exe Status','Elapsed Time','Next run Time'); Title = '检查job执行情况' },
    @{ Script = 'backup.sql';           Props = @('Database Name', 'Compressed Backup Size (MB)','Backup Elapsed Time (sec)','Backup Finish Date'); Title = '检查备份状态' },
    @{ Script = 'sqllog.sql';           Props = @('LogDate', 'ProcessInfo','Text');                   Title = 'SQL Server日志' }
)

foreach ($item in $checkItems) {
    $reportSections += Get-SqlResult -scriptName $item.Script -properties $item.Props -sectionTitle $item.Title
}

# 处理操作系统日志
$osLog = try {
    $events = Get-WinEvent -LogName 'System' -MaxEvents $config.EventLogLimit -ErrorAction Stop | 
              Select-Object EventID,
                            @{Name='TimeCreated'; Expression={$_.TimeCreated.ToString("yyyy-MM-dd HH:mm")}},
                            @{Name='Level'; Expression={
                                switch($_.Level) {
                                    0 { "未分类" }
                                    1 { "关键" }
                                    2 { "错误" }
                                    3 { "警告" }
                                    4 { "信息" }
                                    5 { "详细" }
                                    default { $_.Level }
                                }
                            }},
                            Message

    $events | ConvertTo-Html -Fragment -Property EventID, TimeCreated, Level, Message
}
catch {
    "<p style='color: red'>错误: $_</p>"
}

$reportSections += @"
<h4>操作系统系统日志（最近$($config.EventLogLimit)条）</h4>
$osLog
"@

# 生成完整报告
$fullReport = $reportHeader + ($reportSections -join "`n") + "</body></html>"

# 保存报告
if (Test-Path $config.ReportPath) {
    Remove-Item $config.ReportPath -Force
}

$fullReport | Out-File $config.ReportPath -Encoding UTF8

Write-Host "报告已生成: $($config.ReportPath)" -ForegroundColor Green

喜欢这篇文章，欢迎动动你发财的小手点个赞👍！

关注我，学习更多的数据库知识！

请添加图片描述