MSSQL-最佳實踐-如何監控備份還原進度

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備份還原專題系列月報分享後，我們完成了：三種常見的資料庫備份、備份政策的制定、查找備份鍊、資料庫的三種恢複模式與備份之間的關系、利用檔案組實作冷熱資料隔離備份方案以及本期月報分享的如何監控備份還原進度總共六篇。