Transact-SQL スナップショット バックアップを作成する

適用対象: SQL Server 2022 (16.x)

この記事では、Transact-SQL スナップショット バックアップの使用に関する概要、理由、方法について説明します。 Transact-SQL スナップショット バックアップは、SQL Server 2022 (16.x) の新機能です。


データベースは日に日に大きくなります。 従来、SQL Server のバックアップはストリーミング バックアップです。 ストリーミング バックアップは、データベースのサイズに左右されます。 バックアップ操作はリソース (CPU、メモリ、I/O、ネットワーク) を使い、バックアップの間、同時 OLTP ワークロードのスループットに影響を及ぼします。 データのサイズに左右されることなく、バックアップのパフォーマンスを一定にする方法の 1 つは、基になるストレージ ハードウェアまたはサービスによって提供されるメカニズムを使って、スナップショット バックアップを実行することです。

バックアップ自体はハードウェア レベルで行われるため、これは純粋な SQL Server ソリューションではありません。 まず SQL Server を使ってスナップショット用のデータとログ ファイルを準備し、後で確実にファイルを復元できる状態にします。 これが完了すると、SQL Server 上の I/O は停止し、バックアップ アプリケーションに制御が引き継がれ、スナップショットが完了します。 スナップショットが正常に完了すると、アプリケーションから SQL Server に制御が戻り、I/O が再開されます。 スナップショット操作の間は I/O を停止する必要があるため、サーバー上のワークロードが長時間中断されないように、スナップショットをすばやく実行することが重要です。 これまで、ユーザーは、スナップショット バックアップを完了するために、SQL Writer サービスに基づいて構築されたサードパーティ製ソリューションに頼ってきました。 SQL Writer サービスは、SQL Server とディスクレベル スナップショット間のオーケストレーションを実行するために Windows VSS (ボリューム シャドウ コピー サービス) と SQL Server VDI (仮想デバイス インターフェイス) に依存しています。 SQL Writer サービスに基づくバックアップ クライアントは複雑になる傾向があります。また、Windows 上でのみ動作します。 T-SQL スナップショット バックアップの場合、一連の T-SQL コマンドを使ってオーケストレーションの SQL Server 側を処理できます。 そのため、Windows または Linux 上で動作する独自の小さいバックアップ アプリケーションや、基となるストレージがスナップショットを開始するスクリプト インターフェイスをサポートしている場合はスクリプト対応ソリューションを作成することができます。

こちらのサンプル PowerShell スクリプトを参照してください。これは、SQL Server 2022 (16.x) (とそれ以降) で導入された T-SQL スナップショット バックアップ機能を使って、Azure SQL IaaS 仮想マシンのデータベースをバックアップおよび復元するエンドツーエンド ソリューションの例です。

Workflow

T-SQL スナップショット バックアップ構文を使って、ベンダー依存のスナップショット メカニズムを、一時停止とバックアップの操作から切り離します。 この構文を使うと、次のことができます。

  1. ALTER コマンドを使ってデータベースを停止し、基となるストレージのスナップショットを実行できるようにします。 その後、データベースを展開し、BACKUP コマンドを使ってスナップショットを記録することができます。
  2. 新しい BACKUP GROUP と BACKUP SERVER の各コマンドを使って、複数のデータベースのスナップショットを同時に実行します。 こうすることで、基となるストレージのスナップショットの細分性でスナップショットを実行できるので、同じディスクのスナップショットを複数回実行する必要がなくなります。
  3. FULL バックアップと COPY_ONLY FULL バックアップを実行します。 これらのバックアップは msdb にも記録されます。
  4. スナップショット FULL バックアップの後、通常のストリーミング方法で取得したログ バックアップを使ってポイントインタイム リストアを実行します。 必要に応じて、ストリーミング差分バックアップもサポートされます。

注意

ALTER コマンドを使ってデータベースを一時停止するときの最初のステージで、差分ビットマップはクリアされます。 スナップショットの失敗やその他の理由で、ユーザーがバックアップを実行せずにデータベースの展開を決定した場合、差分ビットマップは無効となります。 そのため、それ以降の差分バックアップは I/O がより多くなります。これは、差分バックアップを行うためにデータベース全体をスキャンする必要があるからです。 スナップショット バックアップが成功すると、差分ビットマップは再び有効になります。

次の図は、T-SQL スナップショット バックアップの概要ワークフローを表しています。

Diagram that shows process from suspend, to snapshot, to backup.

中間のスナップショット手順では、基となるストレージ上でスナップショットを開始する必要があります。 次の図は、バックアップ スクリプトが SQL Server と連携して機能し、スナップショット バックアップ プロセスを完了する処理の例を示しています。

Diagram shows example of how the backup script can work with SQL Server to complete the backup process.

同様に、復元スクリプトは次のように動作します。

Diagram shows how the restore script can work with SQL Server to complete the restore task from a snapshot backup.

制限事項

この機能を使用してバックアップできるデータベースの最大数は 64 です。 サーバーに 64 を超えるデータベースがある場合は、次のエラーが表示されます。

Error message:
Msg 925, Level 19, State 1, Line 4
Maximum number of databases used for each query has been exceeded. The maximum allowed is 64.

以下のセクションでは、ディスクへのスナップショット バックアップを実行するために使われるさまざまな T-SQL コマンドについて説明します。 スナップショット バックアップをディスクに書き込むと、スナップショット バックアップに接続されたメタデータのみがファイルに書き込まれます。 出力には、ヘッダーとファイルの内容を除き、データベースの内容は含まれていません。 完全なバックアップを作成するには、スナップショット バックアップの一環で作成されたシェル ファイルを、実際のスナップショット URI と共に使う必要があります。 このファイルからデータベースの RESTORE を実行するには、RESTORE コマンドを発行する前に、スナップショット URI からマウント ポイントにデータベース ファイルをコピーする必要があります。 ユーザーは、RESTORE DATABASE と共に、RESTORE HEADERONLY、RESTORE FILELISTONLY などの従来のすべての T-SQL コマンドをこのスナップショット バックアップのメタデータ ファイルに対して実行できます。 この構文は、スナップショット バックアップ メタデータの DISK または URL への書き込みをサポートします。 ストリーミング バックアップ セットと同様に、スナップショット バックアップ セットを 1 つのファイルに追加できます。

注意

URL へのバックアップの場合、ブロック BLOB をお勧めしますが、Windows 上の SQL Server ではページ BLOB がサポートされます。 Linux とコンテナー上の SQL Server では、ブロック BLOB のみがサポートされます。

スナップショット バックアップのために 1 つのユーザー データベースを一時停止し、データベース バックアップを記録する

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

スナップショット バックアップのために複数のユーザー データベースを一時停止する

同じ基になるディスク上に複数のデータベースがある場合は、次のコマンドを使用して複数のデータベースを一時停止できます。

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));

BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

サーバー上のすべてのユーザー データベースを一時停止し、スナップショット バックアップを実行する

サーバー上のすべてのユーザー データベースを一時停止する必要がある場合は、次のコマンドを使用します。

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

注意

次のコマンドのいずれも、システム データベースの一時停止をサポートしていません: mastermodel、および msdb (スナップショット バックアップ用)。

1 つのコマンドを使って複数のユーザー データベースを一時停止します

サーバー上のすべてのユーザー データベースのスナップショットを 1 つのバックアップ セットに記録します。

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2));

BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

注意

既定では、スナップショット バックアップ コマンドのための一時停止によって、差分ビットマップはクリアされます。 コピーのみのバックアップを実行する場合は、次のように COPY_ONLY キーワードを使います。

コピーのみのスナップショット バックアップの実行

差分ビットマップは停止前にクリアされるので、SUSPEND_FOR_SNAPSHOT_BACKUP には、停止前に差分ビットマップをクリアしないオプション (COPY_ONLY) が用意されています。

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);

BACKUP DATABASE testdb1
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(GROUP = (testdb1, testdb2), MODE = COPY_ONLY);

BACKUP GROUP testdb1, testdb2
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON
(MODE = COPY_ONLY);

BACKUP SERVER
TO DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FORMAT;

注意

スナップショット バックアップのためにデータベースを一時停止するときに既に指定されているため、BACKUP コマンドで COPY_ONLY を使用する必要はありません。

バックアップセットにタグを付ける

バックアップ コマンドの MEDIANAME オプションと MEDIADESCRIPTION オプションを使用して、スナップショットに関連付けられている URI にタグを付けます。 この使用により、バックアップ ファイルは、基になるスナップショット情報とデータベース メタデータを伝送できます。 NAME オプションと DESCRIPTION オプションを使用して、個々のバックアップセット スナップショットで URI にタグを付けることもできます。

SQL Server では、LABEL 情報は何も解釈されません。 ただし、ユーザーが RESTORE LABELONLY コマンドを使用して、スナップショット バックアップに関連付けられている URI を表示するのに役立ちます。

その後、URI にあるスナップショット ディスクを VM にアタッチして、スナップショットを復元できます。 MEDIANAME と MEDIADESCRIPTION に格納されているスナップショット URI は、後で msdb データベース テーブル msdb.dbo.backupmediaset で表示することもできます。

RESTORE HEADERONLY を使ったスナップショット バックアップの出力

データベース、グループ、サーバーを順番に実行し、同じ出力ファイルに書き込む場合、RESTORE HEADERONLY を使った出力は次のようになります。

RESTORE HEADERONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;

RESTORE FILELISTONLY を使ったスナップショット バックアップの出力

RESTORE FILELISTONLY を使った出力には、既定で最初のバックアップ セットが表示されます。

RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY;

RESTORE FILELISTONLY 出力を 1 つのバックアップ セットにフィルター処理する

RESTORE FILELISTONLY を使って複数のバックアップ セットから特定のバックアップ セットを選ぶには、RESTORE FILELISTONLY で既にサポートされている FILE 句を使います。

RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3;

Screenshot of SSMS output to backups set from query.

RESTORE FILELISTONLY の出力を 1 つのデータベースにフィルター処理する

RESTORE FILELISTONLY を使って選んだバックアップ セット内の複数のデータベースから、さらにフィルター処理で 1 つのデータベースを選ぶには、FILE 句と共に新しく導入された DBNAME 句を使います。 DBNAME 句は、スナップショット バックアップ セットに対してのみ使用できます。

RESTORE FILELISTONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3';

Screenshot of results of filtering RESTORE FILELISTONLY output to a database.

スナップショット データベースを復元する

スナップショット バックアップからデータベースを復元することは、データベースの "アタッチ" と似ています。 データベースを復旧せずにアタッチする必要がある場合は、RECOVERY オプションを指定せずに restore コマンドを実行します。 既定では、RESTORE によってスナップショット バックアップ セット内の最初のデータベースが選ばれます。 次の例では、testdb1 を復元します。 testdb1 が既にサーバー上に存在する場合は、REPLACE 句を含めます。 RESTORE を実行する前に、データベース ファイルをマウントする必要があります。

RESTORE DATABASE testdb1
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, REPLACE, --> no DBNAME clause - restore first database in backup set
MOVE 'testdb1' TO 'd:\temp\snap\testdb1.mdf',
MOVE 'testdb1_log' TO 'd:\temp\snap\testdb1_log.ldf';

一覧の中間にあるスナップショット データベースを復元する

RESTORED を実行する必要があるデータベースが中間にある場合は、DBNAME 句で復元するデータベースを指定します。 次の構文では、DBNAME 句で指定したデータベースを復元します。

RESTORE DATABASE testdb3
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> restores testdb3 database
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;

データベースを別の名前で復元する

データベースは別の名前で復元することができます。 RESTORED を実行する必要があるデータベースが中間にある場合は、DBNAME 句で復元するデータベースを指定します。 次の構文では、DBNAME 句を使って指定したデータベースを復元し、名前を testdb33 に変更します。

RESTORE DATABASE testdb33 --> renames the specified database testdb3 to testdb33.
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb3', --> original name specified here
MOVE 'testdb3' TO 'd:\temp\snap\testdb3.mdf',
MOVE 'testdb3_log' TO 'd:\temp\snap\testdb3_log.ldf',
NORECOVERY;

RESTORE BACKUPSETONLY を使って、複数のデータベースを含むバックアップ セットからデータベースを抽出する

グループまたはサーバー スナップショットの複数のデータベースを含むスナップショット バックアップ セットは、RESTORE BACKUPSETONLY コマンドを使って分割できます。 この結果、データベースごとに 1 つのバックアップ セットが生成されます。

1 つのバックアップ セットを含むバックアップ ファイル内で、1 つのサーバー スナップショットに 3 つのデータベースが含まれている場合、次のコマンドを実行すると、3 つのバックアップ セット (各データベースに 1 つずつ) が生成されます。 出力ファイル用に <file_name_prefix>_<unique_time_stamp> を含むディレクトリが作成されます。

RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db1.bkm'
WITH METADATA_ONLY;

RESTORE BACKUPSETONLY を使って、複数のデータベースを含むバックアップ セット内の特定のデータベースを抽出する

バックアップ セット内の 3 つのデータベースのうち 1 つのデータベースを出力する場合、RESTORE BACKUPSETONLY は DBNAME パラメーターをサポートしています。 また、バックアップ ファイル内の複数のバックアップ セットをフィルター処理する FILE パラメーターもサポートしています。

RESTORE BACKUPSETONLY
FROM DISK = 'd:\temp\db.bkm'
WITH METADATA_ONLY, FILE = 3, DBNAME = 'testdb2';

獲得した一時停止状態とロックを確認できる動的管理ビュー (DMV)

sys.dm_server_suspend_status (db_id, db_name, suspend_session_id, suspend_time_ms, is_diffmap_cleared, is_writeio_frozen)
sys.dm_tran_locks (resource_type, resource_database_id, resource_lock_partition, request_mode, request_type, request_status, request_owner_type, request_session_id)

T-SQL スナップショット バックアップのバックアップ セットの詳細を一覧表示する

SELECT database_name,
    type,
    backup_size,
    backup_start_date,
    backup_finish_date,
    is_snapshot
FROM msdb.dbo.backupset
WHERE is_snapshot = 1;

スナップショット バックアップのためにデータベースが一時停止されたかどうかを確認するサーバーおよびデータベース レベルのプロパティ

SELECT SERVERPROPERTY('SuspendedDatabaseCount');
SELECT SERVERPROPERTY('IsServerSuspendedForSnapshotBackup');
SELECT DATABASEPROPERTYEX('db1', 'IsDatabaseSuspendedForSnapshotBackup');

T-SQL トラブルシューティング の サンプル スクリプト

次の T-SQL サンプル スクリプトを使用して、サーバー上で一時停止されたデータベースを検出し、必要に応じてそれらの一時停止を解除できます。

IF (SERVERPROPERTY('IsServerSuspendedForSnapshotBackup') = 1)
BEGIN
    --full server suspended, requires server level thaw
    PRINT 'Full server is suspended, requires server level thaw'

    ALTER SERVER CONFIGURATION
    SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF
END
ELSE
BEGIN
    IF (SERVERPROPERTY('SuspendedDatabaseCount') > 0)
    BEGIN
        DECLARE @curdb SYSNAME
        DECLARE @sql NVARCHAR(500)

        DECLARE mycursor CURSOR FAST_FORWARD
        FOR
        SELECT db_name
        FROM sys.dm_server_suspend_status;

        OPEN mycursor

        FETCH NEXT
        FROM mycursor
        INTO @curdb

        WHILE @@FETCH_STATUS = 0
        BEGIN
            PRINT 'unfreezing DB ' + @curdb

            SET @sql = 'ALTER DATABASE ' + @curdb + ' SET SUSPEND_FOR_SNAPSHOT_BACKUP = OFF'

            EXEC sp_executesql @SQL

            FETCH NEXT
            FROM mycursor
            INTO @curdb
        END

        PRINT 'All DB unfrozen'

        CLOSE mycursor;

        DEALLOCATE mycursor;
    END
    ELSE
        -- no suspended database, thus no user action needed.
        PRINT 'No database/server is suspended for snapshot backup'
END

関連項目