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

[アーティクル]
10/16/2024

適用対象: 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 スナップショットバックアップ構文を使って、ベンダー依存のスナップショットメカニズムを、一時停止とバックアップの操作から切り離します。この構文を使うと、次のことができます。

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

Note

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

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

中間のスナップショット手順では、基となるストレージ上でスナップショットを開始する必要があります。次の図は、バックアップスクリプトが 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 HEADERONLY、RESTORE 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

次のコマンドのいずれも、スナップショットバックアップ用のシステムデータベース (master、model、および 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) が別のドライブにある場合、次のようにスナップショットバックアップを実行できます。

データベースを中断します (これにより、データファイルとログファイルの両方で書き込み I/O がフリーズします)。
```
ALTER SERVER CONFIGURATION
SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON;
```
データベースのデータファイルとログファイルが存在するすべての基盤となるディスクのスナップショットを取得します。この手順はハードウェアに依存します。
METADATA_ONLY オプションを使用してバックアップを実行し、スナップショットバックアップメタデータ (.bkm) を含む出力を作成します。
```
BACKUP DATABASE testdb1
TO DISK = 'D:\Temp\db.bkm'
WITH METADATA_ONLY;
```

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

復元する VM にスナップショットディスクをマウントまたはアタッチします。
データベースの復元を実行する際には、(前の一覧の手順 3 から) .bkm ファイルを使用します。
復元中にドライブが異なる場合は、論理ファイルの 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 BACKUPSETONLY は DBNAME パラメーターをサポートしています。また、バックアップファイル内の複数のバックアップセットをフィルター処理する 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

次の方法で共有

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

ワークフロー

制限事項

例

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

フィードバック

その他のリソース

次の方法で共有

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

ワークフロー

制限事項

例

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

関連するコンテンツ

フィードバック

その他のリソース

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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