MSSQL-最佳实践-如何监控备份还原进度

2022-04-08 09:21:00

title: MSSQL · 最佳实践 · 如何监控备份还原进度

author: 风移

摘要

本期月报是SQL Server备份还原专题分享系列的第六期，打算分享给大家如何监控SQL Server备份还原进度。

场景引入

由于SQL Server备份还原操作是重I/O读写操作，尤其是当数据库或数据库备份文件比较大的到时候。那么，我们就有强烈的需求去监控备份还原的过程，时时刻刻把握备份还原的进度，以获取备份还原操作完成时间的心理预期以及对系统的影响。本期月报分享如何监控SQL Server备份还原进度。

监控备份还原进度

在SQL Server数据库中，监控数据库备份还原进度方法主要有以下三种：
利用SSMS的备份、还原进度百分比
利用T-SQL的stats关键字展示百分比
利用动态视图监控备份、还原完成百分比

利用SSMS

监控数据库备份进度

在SSMS中，右键点击你需要备份的数据库 => Tasks => Back Up...

在Destination中选择Disk => Add... => 选择备份文件本地存储路径 => OK

在该窗口的左下角部分，会有Process的进度展示，比如截图中的进度表示数据库已经备份完成了30%。
这种方法可以看到数据库备份进程进度的百分比，但是没有更多的详细信息。

监控数据库还原进度

监控数据库还原进度方法与上面的方法十分类似，只是入口不同。还原数据库入口：右键点击你需要还原的数据库 => Tasks => Restore => Database...

在Restore Database页面，选择Device => 点击右侧的预览按钮 => Add => 添加本地备份文件 => OK

在接下来的数据库还原页面中的最右上角部分，有数据库的还原进度条，以及还原百分比。比如，图中的数据库还原进度是50%，参见如下截图：

利用T-SQL

以上方法介绍使用SSMS来备份或者还原数据库进度监控查看方法。当然，有的人喜欢使用T-SQL脚本的方式来备份或者还原数据库。我们同样可以实现备份还原数据库的进度监控，方法是在语句中增加stats关键字，比如stats=10，那么系统在完成每个百分之十以后，都会在Messages中打印出** percent processed的字样。

BACKUP DATABASE [TestBackUpRestore]
TO DISK='C:\BACKUP1\TestBackUpRestore_FULL.bak' WITH STATS=10;

参见如下截图，在Messages窗口中，每个10%，都有** percent processed的进度提示。

注意：
还原数据库的方法相同，同样也是添加stats关键字。比如：

USE [master]
RESTORE DATABASE [TestBackUpRestore] FROM  DISK = N'C:\BACKUP1\TestBackUpRestore_FULL.bak' WITH  FILE = 4,  NOUNLOAD,  STATS = 10

GO

利用DMV

有的人可能会遇到这样的情况：我在做数据库备份还原的时候，忘记添加stats关键字了，Messages窗口什么也没有提示。这种情况下，我该如何去监控我的备份或者还原数据库进度呢？
其实，这种情况也无需紧张，我们同样有办法来监控数据库备份还原的进度，方法是使用动态管理视图sys.dm_exec_requests配合一些关键信息字段来监控进度。方法如下：

USE master
GO

SELECT 
    req.session_id, 
    database_name = db_name(req.database_id),
    req.status,
    req.blocking_session_id, 
    req.command,
    [sql_text] = Substring(txt.TEXT, (req.statement_start_offset / 2) + 1, (
                (
                    CASE req.statement_end_offset
                        WHEN - 1 THEN Datalength(txt.TEXT)
                        ELSE req.statement_end_offset
                    END - req.statement_start_offset
                    ) / 2
                ) + 1),
    req.percent_complete,
    req.start_time,
    cpu_time_sec = req.cpu_time / 1000,
    granted_query_memory_mb = CONVERT(NUMERIC(8, 2), req.granted_query_memory / 128.),
    req.reads,
    req.logical_reads,
    req.writes,
    eta_completion_time = DATEADD(ms, req.[estimated_completion_time], GETDATE()),
    elapsed_min = CONVERT(NUMERIC(6, 2), req.[total_elapsed_time] / 1000.0 / 60.0),
    remaning_eta_min = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0),
    eta_hours = CONVERT(NUMERIC(6, 2), req.[estimated_completion_time] / 1000.0 / 60.0/ 60.0),
    wait_type,
    wait_time_sec = wait_time/1000, 
    wait_resource
FROM sys.dm_exec_requests as req WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as txt 
WHERE req.session_id>50
    AND command IN ('BACKUP DATABASE', 'BACKUP LOG', 'RESTORE DATABASE', 'RESTORE LOG')

由于结果集宽度过宽，人为分割为两个部分来展示查询结果集：

这个结果中有非常多重要的字段信息，比如：
Command: 表示命令种类，此处表示备份数据库命令
sql_text: 语句详细信息，此处展示了完整的T-SQL语句
percent_complete: 进度完成百分比，此处已经完成了59.67%
start_time：进程开始执行时间
eta_completion_time：进程预计结束时间
等等。这种方法除了可以监控数据库备份还原进度外，还可以获取更多的进程信息，是比较推荐的方法。
提示：
这种方法不仅仅是可以用来监控你的备份还原进程，任何其他的用户进程都可以使用类似的方法来监控，你只需要把WHERE语句稍作修改即可。比如：想要监控某一个进程的进度情况，你只需要把WHERE语句修改为WHERE req.session_id=xxx即可。

获取备份历史信息

以上章节是介绍如何监控SQL Server备份还原进程的进度，我们有时也会遇到如下场景是：我们需要如何去探索或者发现某个数据库的备份历史记录信息？参见如下代码可以获取到数据库TestBackUpRestore的历史备份记录信息。

use msdb
GO
DECLARE
    @database_name sysname
;

SELECT
    @database_name = N'TestBackUpRestore'
;

SELECT
    bs.server_name,
    bs.user_name,
    database_name = bs.database_name,
    start_time = bs.backup_start_date,
    finish_tiem = bs.backup_finish_date,
    time_cost_sec = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date),
    back_file = bmf.physical_device_name,
    backup_type = 
    CASE 
        WHEN bs.[type] = 'D' THEN 'Full Backup' 
        WHEN bs.[type] = 'I' THEN 'Differential Database' 
        WHEN bs.[type] = 'L' THEN 'Log' 
        WHEN bs.[type] = 'F' THEN 'File/Filegroup' 
        WHEN bs.[type] = 'G' THEN 'Differential File'
        WHEN bs.[type] = 'P' THEN 'Partial'  
        WHEN bs.[type] = 'Q' THEN 'Differential partial' 
    END,
    backup_size_mb = ROUND(((bs.backup_size/1024)/1024),2),
    compressed_size_mb = ROUND(((bs.compressed_backup_size/1024)/1024),2),
    bs.first_lsn,
    bs.last_lsn,
    bs.checkpoint_lsn,
    bs.database_backup_lsn,
    bs.software_major_version,
    bs.software_minor_version,
    bs.software_build_version,
    bs.recovery_model,
    bs.collation_name,
    bs.database_version
FROM msdb.dbo.backupmediafamily bmf WITH(NOLOCK)
    INNER JOIN msdb.dbo.backupset bs WITH(NOLOCK)
    ON bmf.media_set_id = bs.media_set_id
WHERE bs.database_name = @database_name
ORDER BY bs.backup_start_date DESC

截图如下：

这里需要特别注意：
如果你删除数据库时，使用了msdb.dbo.sp_delete_database_backuphistory存储过程清空数据库的备份历史，将无法再获取到该数据库的备份历史。比如：

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'TestBackUpRestore'
GO

最后总结

继前面五篇SQL Server备份还原专题系列月报分享后，我们完成了：三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案以及本期月报分享的如何监控备份还原进度总共六篇。

码农公寓