SQL Serverのベストプラクティス：バックアップとリストアの進捗状況の監視

8657 ワード
Alibabaクラウド AlibabaCloud AlibabaCloud テキストリンク

バックアップとリストアの進捗状況の監視

SQL Server データベースでは、データベースのバックアップとリストアの進行状況を監視するために、主に 3つの方法があります。

1、SSMSを使用して、バックアップとリストアの進行状況のパーセンテージを監視します。
2、T-SQL stats キーワードを使用してパーセンテージを表示します。
3、ダイナミックビューを使用して、バックアップとリストアの完了の割合を監視します。

本ブログは英語版からの翻訳です。オリジナルはこちらからご確認いただけます。一部機械翻訳を使用しております。翻訳の間違いがありましたら、ご指摘いただけると幸いです。

SSMSを使用する

データベースバックアップの進捗状況の監視
SSMSで、バックアップするデータベースを右クリックし、「Tasks」>「Back Up...」をクリックします。

宛先で、「Disk」>「Add...」を選択します。> バックアップファイルのローカルストレージパスを選択します。

ウィンドウの左下には、処理の進捗状況が表示されています。例えば、スクリーンショットの進捗状況を見ると、データベースの30%がバックアップされていることがわかります。
この方法では、データベースのバックアップの進捗状況をパーセンテージで見ることができますが、それ以上の詳細は表示されません。

データベースのリストアの進捗状況の監視
データベースのリストアの進行状況を監視するこの方法は、エントリポイントが異なることを除いて、上記の方法と似ています。データベースをリストアするためのエントリーポイント：リストアするデータベースを右クリックし、Tasks > Restore> Database...

「データベースの復元」ページで、「Device」を選択し、右側の「Preview」ボタンをクリックし、「Add」>「Add the local backup file」>「OK」をクリックします。

次の「データベースの復元」ページの右上に、データベースの復元進捗状況バーと復元率が表示されます。例えば、図のデータベースの復元進捗状況は、次のスクリーンショットのように50%です。

T-SQLを使う

上記の方法では、SSMSを使用してデータベースのバックアップまたはリストアの進行状況を監視し、表示する方法を説明しています。もちろん、T-SQLスクリプトを使用してデータベースをバックアップまたはリストアすることを好む人もいます。ステートメントにstatsキーワードを追加することで、データベースのバックアップやリストアの進捗状況を監視することもできます。例えば、stats=10の場合、システムは進行状況の10パーセントを完了するたびに「**パーセント処理されました」とメッセージに表示します。

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

下のスクリーンショットを参照してください。メッセージウィンドウでは、10%の進捗が完了するたびに「**%処理されました」と表示される進捗プロンプトが表示されます。

注:
statsキーワードを追加して、同じようにデータベースをリストアします。例えば、以下のようにします。

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

GO

DMVを使用する

ユーザーは、データベースのバックアップとリストアの際にstatsキーワードを追加するのを忘れてしまうことがあり、メッセージウィンドウでは何も促されません。この場合、データベースのバックアップとリストアの進行状況を監視するにはどうすればいいのでしょうか？

この場合、データベースのバックアップとリストアの進捗状況を監視する別の方法があるので問題ありません。Dynamic Management View 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')

人為的に2つの部分に分けて、クエリの結果集合が広すぎるために、クエリの結果集合を表示しています。


結果は、多くの重要なフィールド情報を提供します。例えば、

コマンド: コマンドタイプを示し、ここではバックアップデータベースのコマンドを示します。

sql_text: 文の詳細、ここに完全なT-SQL文を表示します。

percent_complete: 完了した進行状況の割合（ここでは59.67

start_time: プロセスの開始時刻

eta_completion_time: プロセスの終了予定時間

などの情報を得ることができます。この方法は、データベースのバックアップやリストアの進捗状況を把握することができ、より多くのプロセス情報を得ることができるのでおすすめです。

注：
このメソッドは、WHERE文を少し修正するだけで、バックアップとリストアのプロセスだけでなく、他のユーザープロセスを監視するために使用することができます。例えば、プロセスの進行状況を監視したい場合、WHERE文を修正して、WHERE req.session_id=xxxとするだけです。

＃バックアップ履歴情報の取得
上記のセクションでは、SQL Server Backup & Restore プロセスの進行状況を監視する方法について説明していますが、以下のようなシナリオに遭遇することがあります。どのようにしてデータベースのバックアップ履歴情報を発見または探索しますか？データベース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 バックアップ＆リストアの進捗状況を監視して、障害やエラーを最小限に抑える方法を学びました。また、SSMS、T-SQL、ダイナミックビューを使用するなど、データベースのバックアップとリストアの進行状況を監視する3つの異なる方法について説明しました。

アリババクラウドは日本に２つのデータセンターを有し、世界で60を超えるアベラビリティーゾーンを有するアジア太平洋地域No.1(2019ガートナー)のクラウドインフラ事業者です。
アリババクラウドの詳細は、こちらからご覧ください。
アリババクラウドジャパン公式ページ