次の方法で共有


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

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

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


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

バックアップ自体はハードウェア レベルで行われるため、この機能は純粋な SQL Server ソリューションではありません。 まず SQL Server を使ってスナップショット用のデータとログ ファイルを準備し、後で確実にファイルを復元できる状態にします。 この手順が完了すると、SQL Server で書き込み操作が一時停止し (読み取り要求は引き続き許可される)、バックアップ アプリケーションに制御が引き継がれ、スナップショットが完了します。 スナップショットが正常に完了すると、アプリケーションから SQL Server に制御が戻り、書き込み操作が再開されます。

スナップショット操作の間は書き込み操作をフリーズする必要があるため、サーバー上のワークロードが長時間中断されないように、スナップショットをすばやく実行することが重要です。 これまで、ユーザーは、スナップショット バックアップを完了するために、SQL Writer サービスに基づいて構築された Microsoft 以外のソリューションに頼ってきました。 SQL Writer サービスは、SQL Server とディスクレベル スナップショット間のオーケストレーションを実行するために Windows VSS (ボリューム シャドウ コピー サービス) と SQL Server VDI (仮想デバイス インターフェイス) に依存しています。

SQL Writer サービスに基づくバックアップ クライアントは複雑になる傾向があります。また、Windows 上でのみ動作します。 T-SQL スナップショット バックアップの場合、一連の T-SQL コマンドを使ってオーケストレーションの SQL Server 側を処理できます。 この機能により、ユーザーは独自の小規模なバックアップ アプリケーションを作成できるようになります。これらのアプリケーションは、Windows または Linux 上で実行できるほか、基となるストレージがスナップショットを開始するスクリプト インターフェイスをサポートしている場合は、スクリプト対応ソリューションを作成することができます。

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

ワークフロー

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

  1. ALTER コマンドを使ってデータベースを停止し、基となるストレージのスナップショットを実行できるようにします。 その後、データベースを展開し、BACKUP コマンドを使ってスナップショットを記録することができます。

  2. 新しい BACKUP GROUPBACKUP SERVER の各コマンドを使って、複数のデータベースのスナップショットを同時に実行します。 こうすることで、基となるストレージのスナップショットの細分性でスナップショットを実行できるので、同じディスクのスナップショットを複数回実行する必要がなくなります。

  3. FULL バックアップと COPY_ONLY FULL バックアップを実行します。 これらのバックアップは msdb にも記録されます。

  4. スナップショット FULL バックアップの後、通常のストリーミング方法で取得したログ バックアップを使ってポイントインタイム リストアを実行します。 必要に応じて、ストリーミング差分バックアップもサポートされます。

Note

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

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

中断からスナップショット、バックアップまでのプロセスを示す図。

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

次の図は、バックアップ スクリプトが SQL Server と連携して機能し、バックアップ プロセスを完了する処理の例を示しています。

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

図は、復元スクリプトを SQL Server と連携して、スナップショット バックアップから復元タスクを完了する方法を示しています。

制限事項

この機能を使用してバックアップできるデータベースの最大数は 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 HEADERONLYRESTORE FILELISTONLY などの従来のすべての T-SQL コマンドをこのスナップショット バックアップのメタデータ ファイルに対して実行できます。 この構文は、スナップショット バックアップ メタデータの DISK または URL への書き込みをサポートします。 ストリーミング バックアップ セットと同様に、スナップショット バックアップ セットを 1 つのファイルに追加できます。

Note

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

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

ALTER DATABASE testdb1
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

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

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

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;

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

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

ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;

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

Note

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

D. 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;

Note

既定では、 SUSPEND_FOR_SNAPSHOT_BACKUP コマンドは差分ビットマップをクリアします。 コピーのみのバックアップを実行する場合は、次のように COPY_ONLY キーワードを使います。

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

差分ビットマップはフリーズの前にクリアされるため、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;

Note

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

F. 異なるドライブ上のデータ ファイルとログ ファイルを使用してデータベースをバックアップする

データファイル (.mdf および .ndf) が複数のドライブにまたがるデータベースがあり、トランザクション ログ ファイル (.ldf) が別のドライブにある場合、次のようにスナップショット バックアップを実行できます。

  1. データベースを中断します (これにより、データ ファイルとログ ファイルの両方で書き込み I/O がフリーズします)。

    ALTER SERVER CONFIGURATION
    SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
    
  2. データベースのデータ ファイルとログ ファイルが存在するすべての基盤となるディスクのスナップショットを取得します。 この手順はハードウェアに依存します。

  3. METADATA_ONLY オプションを使用してバックアップを実行し、スナップショット バックアップ メタデータ (.bkm) を含む出力を作成します。

    BACKUP DATABASE testdb1
    TO DISK = 'D:\Temp\db.bkm'
    WITH METADATA_ONLY;
    

このバックアップを後のステージで復元するには、次の手順に従います。

  1. 復元する VM にスナップショット ディスクをマウントまたはアタッチします。

  2. データベースの復元を実行する際には、(前の一覧の手順 3 から) .bkm ファイルを使用します。

  3. 復元中にドライブが異なる場合は、論理ファイルの MOVE オプションを使用して、必要な出力先に配置します。 例については、「例 N」を参照してください。

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

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

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

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

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

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

RESTORE HEADERONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

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

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

RESTORE FILELISTONLY
FROM DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;

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

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

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

クエリから設定されたバックアップへの SSMS 出力のスクリーンショット。

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

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

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

データベースへの RESTORE FILELISTONLY 出力のフィルター処理結果のスクリーンショット。

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

スナップショット バックアップからデータベースを復元することは、データベースの "アタッチ" と似ています。 データベースを復旧せずにアタッチする必要がある場合は、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';

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

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;

O. 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;

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

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

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

Q. 取得した中断状態とロックを監視する

次の動的管理ビュー (DMV) を使用できます。

  • sys.dm_server_suspend_status (中断状態を表示する)
  • sys.dm_tran_locks (取得したロックを表示する)

R. バックアップセットの詳細を一覧表示する

次のサンプル スクリプトでは、Transact-SQL スナップショット バックアップのバックアップセット情報を一覧表示します。

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

S. スナップショット バックアップのためにデータベースが中断されたかどうかを確認する

次のサンプル スクリプトは、スナップショット バックアップのために中断されているデータベースのデータベース レベルのプロパティを出力します。

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

T. 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