マークされたトランザクションを使用して関連するデータベースを一貫した状態に復元する

  • [アーティクル]

適用対象:SQL Server

このトピックは、完全復旧モデルまたは一括ログ復旧モデルを使用する SQL Server データベースのみに関連しています。

関連する更新を複数のデータベース ( 関連データベース) に対して実行する場合、トランザクション マークを使用して、それらのデータベースを論理的に一貫した状態に復旧できます。 ただし、この復旧により、復旧ポイントとして使用されていたマークより後にコミットされたトランザクションはすべて失われます。 マークされたトランザクションの使用が適しているのは、関連データベースをテストする場合や、最近コミットされたトランザクションが失われてもよい場合のみです。

すべての関連データベースの関連トランザクションに定期的にマークを付けることにより、これらのデータベースに共通する一連の復旧ポイントが作成されます。 これらのトランザクション マークはトランザクション ログに記録され、ログ バックアップに格納されます。 障害が発生した場合、各データベースを同じトランザクション マークまで復元し、一貫性のある状態に復旧できます。

注意

データベースのログ バックアップは、各データベースで別々に作成できるため、同時に作成する必要はありません。

次のシナリオで関連データベースを復旧するには、既にすべての関連データベースにマークされたトランザクションが含まれている必要があります。

  • 1 つ以上のトランザクション ログが壊れている。 データベースのセットを、最後のログ バックアップの時点の一貫性のある状態に復元する必要がある。

  • データベースのセット全体を、以前の特定の時点の相互に一貫性のある状態に復元する必要がある。

重要

関連データベースを復旧できるのは、特定の時点までではなく、マークが付けられたトランザクションまでに限られます。

マークされたトランザクションの作成方法の詳細については、このトピックの「マーク付きトランザクションの作成」を参照してください。

マークされたトランザクションを使用するための一般的なシナリオ

マークされたトランザクションを使用するための一般的なシナリオでは、次の手順が実行されます。

  1. 各関連データベースの完全バックアップまたは差分バックアップを作成します。

  2. すべてのデータベースのトランザクション ブロックにマークを付けます。

  3. すべてのデータベースのトランザクション ログをバックアップします。

  4. WITH NORECOVERY を指定してデータベース バックアップを復元します。

  5. WITH STOPATMARK を指定してログを復元します。

マークされたトランザクションの使用に関する考慮事項

名前付きマークをトランザクション ログに挿入する前に、次の点を考慮してください。

  • トランザクション マークはログ領域を使用するので、データベース復旧ストラテジにおいて重要な役割を果たすトランザクションだけに使用する必要があります。

  • マークされたトランザクションのコミットが完了したら、 msdb の logmarkhistoryテーブルに 1 行が挿入されます。

  • マークされたトランザクションが同じデータベース サーバーまたは異なるサーバー上の複数のデータベースと関係している場合は、影響を受けたすべてのデータベースのログにそのマークが記録される必要があります。

マーク付きトランザクションの作成

マーク付きトランザクションを作成するには、 BEGIN TRANSACTION ステートメントと WITH MARK [description] 句を使用します。 省略可能な description は、マークの説明テキストです。 トランザクションのマーク名が必要です。 マーク名は再利用できます。 トランザクション ログには、マーク名、説明、データベース、ユーザー、datetime 情報、および LSN (ログ シーケンス番号) が記録されます。 マーク名と共に datetime 情報を使用することで、マークが一意に識別されます。

データベースのセットでマーク付きトランザクションを作成するには

  1. BEGIN TRAN ステートメントでトランザクションに名前を付け、WITH MARK 句を使用します。

    BEGIN TRAN new_mark_name WITH MARK ステートメントは、既存のトランザクション内で入れ子にすることができます。 トランザクションに名前が付いている場合でも、 new_mark_name の値がそのトランザクションのマーク名になります。

    注意

    入れ子にした 2 番目の BEGIN TRAN...WITH MARK を実行すると、このステートメントはスキップされますが、警告メッセージは生成されます。

  2. セット内のすべてのデータベースに対して更新を実行します。

    BEGIN TRAN...WITH MARK ステートメントが実行されるサーバー インスタンス上でのみ、特定のトランザクションのマークがトランザクション ログに挿入されます。 トランザクション マークは、そのサーバー インスタンス上のマーク付きトランザクションによって更新されたすべてのデータベースのトランザクション ログに配置されます。 データベースが他のサーバー インスタンス上にある場合は、それらのサーバー インスタンスで同一のマークを作成する必要があります。

例

次の例では、トランザクション ログを ListPriceUpdateというマーク付きトランザクションのマークまで復元します。

USE AdventureWorks2022;
GO  
BEGIN TRANSACTION ListPriceUpdate  
   WITH MARK 'UPDATE Product list prices';  
GO  
  
UPDATE Production.Product  
   SET ListPrice = ListPrice * 1.10  
   WHERE ProductNumber LIKE 'BK-%';  
GO  
  
COMMIT TRANSACTION ListPriceUpdate;  
GO  
  
-- Time passes. Regular database   
-- and log backups are taken.  
-- An error occurs in the database.  
USE master  
GO  
  
RESTORE DATABASE AdventureWorks  
FROM AdventureWorksBackups  
WITH FILE = 3, NORECOVERY;  
GO  
  
RESTORE LOG AdventureWorks  
   FROM AdventureWorksBackups   
   WITH FILE = 4,  
   RECOVERY,   
   STOPATMARK = 'UPDATE Product list prices';

他のサーバーへのマークの強制波及

トランザクションが別のサーバーに波及しても、トランザクション マーク名はそのサーバーに自動的には分散されません。 他のサーバーにマークが広まるようにするには、BEGIN TRAN name WITH MARK ステートメントを含むストアド プロシージャを記述する必要があります。 このストアド プロシージャは、元のサーバーのトランザクションの範囲内においてリモート サーバー上で実行されなければなりません。

たとえば、 SQL Serverの複数のインスタンスに存在するパーティション データベースを考えてください。 各インスタンスには、 coyoteという名前のデータベースがあります。 まず、すべてのデータベースで sp_SetMarkなどのストアド プロシージャを作成します。

CREATE PROCEDURE sp_SetMark  
@name nvarchar (128)  
AS  
BEGIN TRANSACTION @name WITH MARK  
UPDATE coyote.dbo.Marks SET one = 1  
COMMIT TRANSACTION;  
GO

次に、すべてのデータベースにマークを挿入するトランザクションを含んでいる、 sp_MarkAll というストアド プロシージャを作成します。 sp_MarkAll は、任意のインスタンスから実行できます。

CREATE PROCEDURE sp_MarkAll  
@name nvarchar (128)  
AS  
BEGIN TRANSACTION  
EXEC instance0.coyote.dbo.sp_SetMark @name  
EXEC instance1.coyote.dbo.sp_SetMark @name  
EXEC instance2.coyote.dbo.sp_SetMark @name  
COMMIT TRANSACTION;  
GO

2 フェーズ コミット (two-phase commit)

分散トランザクションのコミットは、準備とコミットという 2 つのフェーズで発生します。 マーク付きトランザクションがコミットされると、マーク付きトランザクション内の各データベースに関するコミット ログ レコードは、状況不明トランザクションがどのログにも含まれていない時点のログに挿入されます。 これにより、トランザクションのコミット状態がログごとに異なるなどの問題がなくなります。

この処理は、マーク付きトランザクションのコミット中に次の順で行われます。

  1. マーク付きトランザクションの準備フェーズで、すべての新しい準備とコミットが停止します。

  2. 既に準備が完了しているトランザクションのコミットのみが、継続を許可されます。

  3. マーク付きトランザクションは、タイムアウトによってすべての準備されたトランザクションがなくなるのを待ちます。

  4. マーク付きのトランザクションが準備され、コミットされます。

  5. 新しい準備とコミットの停止が解除されます。

複数のデータベースにまたがるマーク付きトランザクションによって引き起こされた機能停止は、サーバーのトランザクション処理パフォーマンスを低下させる可能性があります。

複数のマーク付きトランザクションを同時に実行しないことをお勧めします。 実際にはあまりないことですが、分散されたマーク付きトランザクションのコミットが、同時にコミットされる他のマーク付き分散トランザクションによってデッドロックに陥ることがあります。 その場合、マーク付きトランザクションは、デッドロックの対象として選択され、ロールバックされます。 このエラーが発生した場合、アプリケーションではマーク付きトランザクションを再試行できます。 複数のマーク付きトランザクションのコミットが同時に試行されると、デッドロックが生じる可能性が高くなります。

マークされたトランザクションへの復旧

特定のマークの直前または直前にマークされたトランザクションを含むデータベースを復旧する方法については、「マークされたトランザクションを 含む関連データベースの復旧」を参照してください。

参照

BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)
システム データベースのバックアップと復元 (SQL Server)
BEGIN TRANSACTION (Transact-SQL)
トランザクション ログ バックアップの適用 (SQL Server)
データベースの完全バックアップ (SQL Server)
RESTORE (Transact-SQL)
マークされたトランザクションを含む関連データベースの復旧