SQL Server 数据库判断最近一次备份是否成功
在 SQL Server 环境中，官方提供的 msdb.dbo.backupset 等备份表仅记录成功的任务信息，缺乏直接的备份失败日志，这使得在未接入外部监控系统的情况下难以快速排查故障。通过解析内部默认的 Trace 文件并将其转换为标准数据表，再与 backupset 表进行时间关联比对，即可准确提取每个数据库最近一次任务的执行结果，从而构建自动化告警逻辑。
获取 ErrorLog Trace 文件路径
首先需要定位 SQL Server 内部的跟踪日志（Trace）路径。SQL Server 默认启用的 Trace 会记录包括备份操作在内的详细运行轨迹。借助 sys.traces 系统视图，可以计算出默认的 ErrorLog Trace 文件的物理存储位置：
SELECT
REVERSE(
SUBSTRING(
REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])),
260
)
) + N'log.trc'
FROM
sys.traces
WHERE
is_default = 1此查询通常返回类似服务器盘符下的 log.trc 路径：
将 Trace 文件转换为可查数据表
获得物理路径后，使用内置函数 sys.fn_trace_gettable 可以将 .trc 二进制日志文件转化为结构化的表格数据，便于后续进行标准的 SQL 过滤和检索。
SELECT
*
FROM
sys.fn_trace_gettable(
'S:\MSSQL13.MSSQLSERVER\MSSQL\Log\log.trc',
default
)生成的表格包含所有记录的时序、事件 ID、文本信息及所属数据库名称等字段：
筛选备份相关的事件
在转换后的表中，我们需要关注备份动作。通过 EventClass 识别事件类型，并在 TextData 中筛选关键字段：
- EventClass = 115：对应于数据库备份操作开始执行的系统事件。
- TextData 筛选：以
'BACKUP%'开头的日志语句标志着备份任务的触发点。
SELECT
TextData,Databasename,StartTime
FROM
sys.fn_trace_gettable(
'S:\MSSQL13.MSSQLSERVER\MSSQL\Log\log.trc',
default
)
WHERE
EventClass = 115
AND UPPER(CONVERT(nvarchar(max), TextData)) LIKE 'BACKUP%'执行效果如下所示，可以看到触发的备份指令和发生时间：
由于 Trace 日志只记录了“尝试开始备份”这一动作，并未明确标记最终是否成功写入磁盘，因此必须引入 msdb.dbo.backupset 辅助表进行联合验证。只要 Trace 中出现了备份动作，无论成功与否都会产生该记录。
结合 BackupSet 判断备份成败
核心逻辑在于时间匹配：如果某次 Trace 记录的时间点附近，在 backupset 中也找到了对应的备份开始记录，则判定为成功；反之若无对应记录，极可能意味着备份在执行过程中遭遇错误被中断或未落入元数据表，即判定为失败。
具体的业务逻辑推导流程为：
- 从 Trace 表中获取备份操作的起始时间 (StartTime)。
- 将该时间与
backupset表的backup_start_date进行交叉比对。 - 设定时间容忍阈值（例如相差小于 5 秒），以消除处理延迟造成的匹配偏差。
完整的对比验证逻辑示意：
基于上述思路编写的 SQL 语句可通过左外连接（LEFT OUTER JOIN）来判断：
SELECT
dt.DatabaseName,
dt.StartTime,
bs.backup_start_date,
bs.backup_finish_date,
[Status] = CASE
WHEN bs.backup_start_date IS NULL THEN (dt.DatabaseName) + '数据库备份失败'
ELSE (dt.DatabaseName) + '数据库备份成功'
END
FROM
sys.fn_trace_gettable(
'S:\MSSQL13.MSSQLSERVER\MSSQL\Log\log.trc',
default
) AS dt
LEFT OUTER JOIN msdb.dbo.backupset AS bs ON dt.DatabaseName = bs.database_name
AND ABS(
DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)
) < 5
WHERE
dt.EventClass = 115
AND UPPER(CONVERT(nvarchar(max), dt.TextData)) LIKE N'BACKUP%'
ORDER BY
dt.StartTime DESC;当查询成功运行并正确输出各个库的状态时，界面呈现如预期的分组列表：
生成告警专用的精简 SQL 语句
为了在监控系统中调用，通常要求针对每一个数据库仅输出一行最关键的“最新状态”。如果采用游标遍历 sys.databases 来获取状态，性能开销极大。在实际测试中，逐条查询导致响应缓慢且占用大量 CPU 和内存逻辑读取资源：
DBCC FREE
DECLARE @databaseName1 nvarchar(100)
DECLARE @sql nvarchar(4000)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @databaseName1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
SELECT
TOP 1 dt.DatabaseName,... --省略具体内部拼接逻辑
...'
EXEC sp_executesql @sql
FETCH NEXT FROM db_cursor INTO @databaseName1
END
CLOSE db_cursor
DEALLOCATE db_cursor长时间耗时的测试反馈证明此方法不可用于生产环境：
第1步：优化查询方案
抛弃低效的游标模式，使用单表全量扫描搭配聚合函数 MAX() 配合 GROUP BY 来一次性获取全部数据库的最新状态。这利用了数据库引擎本身的高效批量处理能力：
SELECT
[Status] = MAX(CASE
WHEN bs.backup_start_date IS NULL THEN dt.DatabaseName+'数据库备份失败'
ELSE dt.DatabaseName+'数据库备份成功'
END)
FROM
sys.fn_trace_gettable(
(
SELECT
REVERSE(
SUBSTRING(
REVERSE([path]),
CHARINDEX(CHAR(92), REVERSE([path])),
260
)
) + N'log.trc'
FROM
sys.traces
WHERE
is_default = 1
),
default
) AS dt
LEFT OUTER JOIN msdb.dbo.backupset AS bs ON dt.DatabaseName = bs.database_name
AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5
WHERE
dt.EventClass = 115
AND UPPER(CONVERT(nvarchar(max), dt.TextData)) LIKE 'BACKUP%'
GROUP BY
dt.DatabaseName第2步：验证查询性能
采用上述 Group By 优化方案后，整体耗时从数分钟骤降至一秒钟以内。资源占用大幅降低，使得定时高频轮询成为可能：
SQL Server 分析和编译时间: CPU 时间 = 15 毫秒，占用时间 = 20 毫秒。 (3 行受影响) 表 'Worktable'。扫描计数 0...（显示低负载扫描） 表 'backupset'。扫描计数 1，逻辑读取 48 次...
实际查询产出结果正常，满足应用需求：
至此已具备获取全盘备份健康状态的脚本能力。对于集成外部监控系统（如 Prometheus、Zabbix 等），只需定期抽取该查询结果中的 “Database备份失败” 标签，即可无缝对接发送告警邮件或短信。关于替代性的更底层读取手段（例如直接使用 xp_readerrorlog API）亦存在可行性，但本文未在此作进一步测试扩展：
以上就是通过 Trace 文件和 BackupSet 关联判断 SQL Server 数据库最近一次备份是否失败的详细内容，更多关于利用 SQL 脚本监控数据库健康状态的资料请关注本站其它相关文章！
免责声明：本文内容仅供参考，不代表本站观点。转载或引用请注明出处。
