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 = 10GO利用DMV
有的人可能會遇到這樣的情況：我在做數據庫備份還原的時候，忘記添加stats關鍵字了，Messages窗口什么也沒有提示。這種情況下，我該如何去監控我的備份或者還原數據庫進度呢？
其實，這種情況也無需緊張，我們同樣有辦法來監控數據庫備份還原的進度，方法是使用動態管理視圖sys.dm_exec_requests配合一些關鍵信息字段來監控進度。方法如下：
USE master
GOSELECT 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語句修改為WHERE req.session_id=xxx即可。
獲取備份歷史信息
以上章節是介紹如何監控SQL Server備份還原進程的進度，我們有時也會遇到如下場景是：我們需要如何去探索或者發現某個數據庫的備份歷史記錄信息？參見如下代碼可以獲取到數據庫TestBackUpRestore的歷史備份記錄信息。
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_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備份還原專題系列月報分享后，我們完成了：三種常見的數據庫備份、備份策略的制定、查找備份鏈、數據庫的三種恢復模式與備份之間的關系、利用文件組實現冷熱數據隔離備份方案以及本期月報分享的如何監控備份還原進度總共六篇。
...)
