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

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

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

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

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

一、 图形化 SSMS

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

二、 SQL的stats关键字

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

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

还原进度显示方法相同，也是添加stats关键字。

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_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 req.session_id=xxx即可。

四、 获取备份历史信息

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_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博客