# 安装 SqlServer 模块（如尚未安装）
Install-Module SqlServer -Force -Scope CurrentUser

# 连接到本地默认实例（使用 Windows 集成身份验证）
$instance = Connect-SqlInstance -ServerInstance "localhost"

# 输出实例基本信息
[PSCustomObject]@{
    InstanceName   = $instance.Name
    Version        = $instance.VersionString
    Edition        = $instance.Edition
    ProductLevel   = $instance.ProductLevel
    Platform       = $instance.Platform
    HostName       = $instance.NetName
    Collation      = $instance.Collation
    LoginMode      = $instance.LoginMode
    IsClustered    = $instance.IsClustered
}

# 连接实例
$instance = Connect-SqlInstance -ServerInstance "localhost"

# 遍历所有用户数据库，收集状态和空间信息
$dbStats = foreach ($db in $instance.Databases) {
    # 跳过系统数据库（可选）
    if ($db.IsSystemObject) { continue }

    [PSCustomObject]@{
        DatabaseName    = $db.Name
        Status          = $db.Status
        RecoveryModel   = $db.RecoveryModel
        SizeMB          = [math]::Round($db.Size, 2)
        DataSpaceMB     = [math]::Round(
            ($db.FileGroups | ForEach-Object { $_.Files } |
                Measure-Object -Property Size -Sum).Sum / 1024, 2
        )
        LogSpaceMB      = [math]::Round(
            ($db.LogFiles | Measure-Object -Property Size -Sum).Sum / 1024, 2
        )
        LastBackupDate  = $db.LastBackupDate
        CreateDate      = $db.CreateDate
    }
}

# 按大小降序排列并输出
$dbStats | Sort-Object SizeMB -Descending | Format-Table -AutoSize

# 查询当前实例中活跃会话和阻塞情况
$query = @"
SELECT
    session_id,
    DB_NAME(database_id) AS DatabaseName,
    status,
    wait_type,
    wait_time_ms,
    blocking_session_id,
    cpu_time,
    reads,
    writes,
    logical_reads,
    start_time
FROM sys.dm_exec_requests
WHERE session_id > 50
ORDER BY start_time
"@

$results = Invoke-Sqlcmd -ServerInstance "localhost" -Query $query

# 筛选被阻塞的会话
$blocked = $results | Where-Object { $_.blocking_session_id -ne 0 }

if ($blocked) {
    Write-Host "检测到阻塞会话:" -ForegroundColor Yellow
    foreach ($row in $blocked) {
        [PSCustomObject]@{
            SessionId    = $row.session_id
            Database     = $row.DatabaseName
            Status       = $row.status
            WaitType     = $row.wait_type
            WaitTimeMs   = $row.wait_time_ms
            BlockedBy    = $row.blocking_session_id
            StartTime    = $row.start_time
        }
    }
} else {
    Write-Host "当前无阻塞会话" -ForegroundColor Green
}

# 连接实例并定义备份阈值（小时）
$instance = Connect-SqlInstance -ServerInstance "localhost"
$thresholdHours = 24
$cutoffTime = (Get-Date).AddHours(-$thresholdHours)

# 检查备份状态
$backupReport = foreach ($db in $instance.Databases) {
    if ($db.IsSystemObject) { continue }

    $lastFull    = $db.LastBackupDate
    $lastDiff    = $db.LastDifferentialBackupDate
    $lastLog     = $db.LastLogBackupDate
    $recoveryModel = $db.RecoveryModel.ToString()

    $riskLevel = "正常"
    if ($lastFull -eq [datetime]::MinValue) {
        $riskLevel = "高风险"
    } elseif ($lastFull -lt $cutoffTime) {
        $riskLevel = "警告"
    }

    [PSCustomObject]@{
        Database        = $db.Name
        RiskLevel       = $riskLevel
        RecoveryModel   = $recoveryModel
        LastFullBackup  = if ($lastFull -eq [datetime]::MinValue) { "从未备份" } else { $lastFull }
        LastDiffBackup  = if ($lastDiff -eq [datetime]::MinValue) { "无" } else { $lastDiff }
        LastLogBackup   = if ($lastLog -eq [datetime]::MinValue) { "无" } else { $lastLog }
    }
}

# 按风险等级排序输出
$backupReport | Sort-Object RiskLevel | Format-Table -AutoSize

# 安装 SqlServer 模块
Install-Module -Name SqlServer -Scope CurrentUser -Force

# 导入模块
Import-Module SqlServer

# 使用 Invoke-Sqlcmd 执行查询
$serverInstance = "localhost"
$database = "MyAppDB"

# 简单查询
$results = Invoke-Sqlcmd -ServerInstance $serverInstance -Database $database -Query @"
    SELECT TOP 10
        name AS 表名,
        rows AS 行数,
        CAST(reserved AS FLOAT) / 1024 AS 已用MB
    FROM sys.tables t
    INNER JOIN sys.partitions p ON t.object_id = p.object_id
    ORDER BY rows DESC
"@

$results | Format-Table -AutoSize

# 带参数的查询（防止 SQL 注入）
$params = @{
    ServerInstance = $serverInstance
    Database       = $database
    Query          = "SELECT * FROM Users WHERE Department = @dept AND IsActive = 1"
    Variable       = @(
        (New-Object Microsoft.Data.SqlClient.SqlParameter('@dept', 'Engineering'))
    )
}

$users = Invoke-Sqlcmd @params
$users | Select-Object UserName, Email, CreatedDate |
    Format-Table -AutoSize

function Backup-SqlDatabaseSafe {
    <#
    .SYNOPSIS
    执行 SQL Server 数据库备份
    #>
    param(
        [string]$ServerInstance = "localhost",
        [Parameter(Mandatory)]
        [string]$Database,
        [string]$BackupDir = "C:\Backups\SQL",
        [int]$RetainDays = 7
    )

    $timestamp = Get-Date -Format 'yyyyMMdd-HHmmss'
    $backupFile = Join-Path $BackupDir "${Database}_${timestamp}.bak"

    # 确保备份目录存在
    New-Item -Path $BackupDir -ItemType Directory -Force | Out-Null

    Write-Host "开始备份：$Database" -ForegroundColor Cyan

    Backup-SqlDatabase -ServerInstance $ServerInstance `
        -Database $Database `
        -BackupFile $backupFile `
        -CompressionOption On `
        -CopyOnly

    $size = [math]::Round((Get-Item $backupFile).Length / 1MB, 2)
    Write-Host "备份完成：$backupFile ($size MB)" -ForegroundColor Green

    # 清理过期备份
    Get-ChildItem $BackupDir -Filter "${Database}_*.bak" |
        Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-$RetainDays) } |
        ForEach-Object {
            Remove-Item $_.FullName -Force
            Write-Host "已清理：$($_.Name)" -ForegroundColor DarkGray
        }

    return $backupFile
}

# 执行备份
Backup-SqlDatabaseSafe -Database "MyAppDB" -RetainDays 7

# 恢复数据库
function Restore-SqlDatabaseSafe {
    param(
        [string]$ServerInstance = "localhost",
        [string]$Database,
        [Parameter(Mandatory)]
        [string]$BackupFile
    )

    Write-Host "恢复数据库：$Database" -ForegroundColor Yellow

    # 先设置单用户模式（断开所有连接）
    Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
        ALTER DATABASE [$Database] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
"@

    Restore-SqlDatabase -ServerInstance $ServerInstance `
        -Database $Database `
        -BackupFile $BackupFile `
        -ReplaceDatabase

    # 恢复多用户模式
    Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
        ALTER DATABASE [$Database] SET MULTI_USER
"@

    Write-Host "数据库恢复完成" -ForegroundColor Green
}

function Get-SqlPerformanceReport {
    <#
    .SYNOPSIS
    生成 SQL Server 性能报告
    #>
    param([string]$ServerInstance = "localhost")

    Write-Host "========== SQL Server 性能报告 ==========" -ForegroundColor Cyan

    # 1. 缓冲池使用率
    $bufferPool = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
        SELECT
            CAST(COUNT(*) * 8 / 1024.0 AS DECIMAL(10,2)) AS 总缓存MB,
            CAST(SUM(CASE WHEN is_modified = 1 THEN 8 ELSE 0 END) / 1024.0 AS DECIMAL(10,2)) AS 脏页MB,
            CAST(SUM(CASE WHEN is_modified = 0 THEN 8 ELSE 0 END) / 1024.0 AS DECIMAL(10,2)) AS 干净页MB
        FROM sys.dm_os_buffer_descriptors
"@
    Write-Host "`n缓冲池：总 $($bufferPool.总缓存MB) MB, 脏页 $($bufferPool.脏页MB) MB"

    # 2. Top 10 慢查询
    $slowQueries = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
        SELECT TOP 10
            SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
                ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
                ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS 查询语句,
            qs.execution_count AS 执行次数,
            CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(10,2)) AS 总耗时秒,
            CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(10,4)) AS 平均耗时秒,
            qs.total_logical_reads AS 逻辑读取次数
        FROM sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
        ORDER BY qs.total_elapsed_time DESC
"@

    $slowQueries | Select-Object 查询语句, 执行次数, 总耗时秒, 平均耗时秒 |
        Format-Table -AutoSize -Wrap

    # 3. 阻塞会话
    $blocking = Invoke-Sqlcmd -ServerInstance $ServerInstance -Query @"
        SELECT
            blocking.session_id AS 阻塞源ID,
            blocked.session_id AS 被阻塞ID,
            DB_NAME(blocked.database_id) AS 数据库,
            blocked.wait_type AS 等待类型,
            blocked.wait_time/1000 AS 等待秒数,
            blocking_sql.text AS 阻塞源语句
        FROM sys.dm_exec_requests blocked
        JOIN sys.dm_exec_sessions blocking ON blocked.blocking_session_id = blocking.session_id
        CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql
        CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_sql
"@

    if ($blocking) {
        Write-Host "`n阻塞会话：" -ForegroundColor Red
        $blocking | Format-Table -AutoSize
    } else {
        Write-Host "`n无阻塞会话" -ForegroundColor Green
    }
}

Get-SqlPerformanceReport