sqlserver 如何查看备份还原进度及历史备份信息

有时需要给业务方备份还原数据库，如果库比较大，可能时不时会被问“怎么样啦？”，“还剩多少哇？”，如果看不到监控备份还原的进度就很悲催，答不上来...

SQL Server主要有三种方法备份还原进度：

• 利用SSMS备份还原，查看进度条（还原的不准）
• 利用SQL的stats关键字指定每完成百分之几显示
• 利用动态性能视图监控（未指定stats关键字）

个人感觉还原进度很不准，对于大库可能前面70%左右的时间进度都为0，后面又突然前进飞快，暂时没想明白是为什么

一、 图形化 SSMS

这个最简单也最常用，备份在左下角会有进度

二、 SQL的stats关键字

可以在备份语句中加stats关键字，比如stats=10，每备完10%会在Messages中打印出** percent processed，这种方法最后还会显示总用时和平均速度。

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

三、 利用DMV

如果在备份还原的时候，忘了加stats关键字，是不是就只能一脸懵逼？其实也不是

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_offsetWHEN - 1 THEN Datalength(txt.TEXT)ELSE req.statement_end_offsetEND - 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>50AND 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 req.session_id=xxx即可。

四、 获取备份历史信息

use msdb
GO
DECLARE@database_name sysname
;SELECT@database_name = N'TestBackUpRestore'
;SELECTbs.server_name,bs.user_name,database_name = bs.database_name,start_time = bs.backup_start_date,finish_time = 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';

参考

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

SQL Server 维护脚本分享（12）查看数据库空间分配情况（准确）_公众号：SQLServer-CSDN博客

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！