1.设置当前Windows用户角色
-- 1. 在 msdb 中为登录名创建用户（如果尚未存在） USE msdb; CREATE USER [登录用户] FOR LOGIN [登录用户]; -- 2. 加入只读角色（关键！） ALTER ROLE SQLAgentReaderRole ADD MEMBER [登录用户]; USE msdb; CREATE USER [SKYL\winning] FOR LOGIN [登录用户]; GRANT SELECT ON dbo.sysjobs TO [登录用户]; -- 如果还需作业步骤、历史等，继续授权： GRANT SELECT ON dbo.sysjobsteps TO [登录用户]; GRANT SELECT ON dbo.sysjobhistory TO [登录用户];2.创建备份脚本
命名为：monthly_backup_config_audit.ps1
修改实例名
# 启用严格模式和错误停止 Set-StrictMode -Version Latest $ErrorActionPreference = "Stop" try { Write-Host "开始月度备份任务..." -ForegroundColor Green # --- 原有逻辑放在这里 --- $DateStr = Get-Date -Format "yyyy-MM" $BackupRoot = "C:\DB_Backups\Monthly_Audit_Config\$DateStr" $SqlServerInstance = "localhost" # ← 请根据实际修改，可以用ip,端口！ # 创建目录 $null = New-Item -ItemType Directory -Path $BackupRoot -Force # 导入模块（关键！） Import-Module SqlServer -ErrorAction Stop # 1. 导出配置数据（T-SQL 查询结果保存为 CSV） $ConfigQueries = @{ "Logins.csv" = "SELECT name, type_desc, create_date, is_disabled FROM sys.server_principals WHERE type IN ('S','U','G')" "ServerRoles.csv" = "SELECT sp.name AS login, sr.name AS role FROM sys.server_role_members rm JOIN sys.server_principals sp ON rm.member_principal_id = sp.principal_id JOIN sys.server_principals sr ON rm.role_principal_id = sr.principal_id" "LinkedServers.csv" = "SELECT name, product, provider, data_source FROM sys.servers WHERE is_linked = 1" "Jobs.csv" = "SELECT name, enabled, date_created FROM msdb.dbo.sysjobs" "ConfigSettings.csv" = "SELECT name, value,value_in_use, minimum, maximum, description FROM sys.configurations ORDER BY name;" } foreach ($file in $ConfigQueries.Keys) { $query = $ConfigQueries[$file] $outputPath = Join-Path $BackupRoot $file Invoke-Sqlcmd -ServerInstance $SqlServerInstance -Query $query -OutputAs DataTables | Export-Csv -Path $outputPath -NoTypeInformation -Encoding UTF8 } # 2. 复制默认跟踪文件（如果启用） try { $traceResult = Invoke-Sqlcmd -ServerInstance $SqlServerInstance -Query "SELECT path FROM sys.traces WHERE is_default = 1" if ($null -ne $traceResult -and $traceResult.Count -gt 0) { $DefaultTracePath = $traceResult[0].path # 安全取第一行 if ($DefaultTracePath) { $TraceDir = Split-Path $DefaultTracePath -Parent if (Test-Path $TraceDir) { $TraceFiles = Get-ChildItem -Path $TraceDir -Filter "log_*.trc" | Sort-Object LastWriteTime -Descending | Select-Object -First 5 foreach ($f in $TraceFiles) { Copy-Item $f.FullName -Destination $BackupRoot -ErrorAction SilentlyContinue } Write-Host "默认跟踪文件已备份。" } } } else { Write-Host "默认跟踪未启用，跳过跟踪文件备份。" -ForegroundColor Yellow } } catch { Write-Host "获取默认跟踪路径失败：$($_.Exception.Message)" -ForegroundColor Yellow } # 3. 复制 SQL Server Audit 文件（如有） $AuditPath = "C:\Audits\" # 替换为您实际的 Audit 文件路径 if (Test-Path $AuditPath) { $AuditFiles = Get-ChildItem -Path $AuditPath -Filter "*.sqlaudit" | Where-Object { $_.LastWriteTime -gt (Get-Date).AddMonths(-1) } foreach ($af in $AuditFiles) { Copy-Item $af.FullName -Destination $BackupRoot } } # 4. （可选）导出 Windows 登录事件（最近30天） $EventLogPath = Join-Path $BackupRoot "LoginEvents.evtx" wevtutil epl Application $EventLogPath /q:"*[System[Provider[@Name='MSSQLSERVER'] and (EventID=18456 or EventID=18453)]]" # 5. 压缩归档（可选） Compress-Archive -Path "$BackupRoot\*" -DestinationPath "$BackupRoot.zip" -Force Remove-Item -Path "$BackupRoot\*" -Exclude "*.zip" -Recurse Write-Host "? 月度配置与审计备份完成: $BackupRoot.zip" Write-Host "备份成功完成！" -ForegroundColor Green } catch { Write-Host "脚本执行失败：" -ForegroundColor Red Write-Host $_.Exception.Message -ForegroundColor Yellow Write-Host "堆栈跟踪：$($_.ScriptStackTrace)" -ForegroundColor Gray pause # 防止窗口关闭，便于查看错误 }3.测试备份
按
Win + R，输入powershell，回车 → 打开PowerShell 控制台手动执行脚本
cd C:\Scripts .\monthly_backup_config_audit.ps1查看备份目录是否有文件，如果有的话，即成功，否则需要排查问题；
4.创建任务计划
打开任务计划程序
创建任务 → 名称：
Monthly SQL Config & Audit Backup触发器：每月（如每月1号 2:00 AM）
操作：
程序：
powershell.exe路径参数：
-ExecutionPolicy Bypass -WindowStyle Hidden -File "C:\Script\monthly_backup_config_audit.ps1"
“常规”选项卡：
使用最高权限运行
选择专用服务账户（如
svc-sqlbackup）
