SQL Serverのシステムデータベースをリストアする

本稿は、ZOZO Advent Calendar 2024 シリーズ 6 の20日目の記事です。

はじめに

ZOZOTOWNでは、サービスのメインデータベースとしてSQL Serverを採用しています。通常、SQL Serverのシステムデータベースをリストアする機会は少ないものの、その手順はユーザーデータベースとは異なるため、今回の記事でその方法を記録しておきたいと思います。

SQL Serverのシステムデータベースとは

SQL Serverのシステムデータベースは、SQL Serverの運用や管理に不可欠なデータベースで、SQL Serverインスタンスの動作を支える役割を果たします。これらのデータベースはユーザーデータベースとは異なり、主にSQL Server自体の内部処理や構成情報を保持しています。

普段はあまり操作することはありませんが、システムデータベースが存在しないとSQL Serverは動作しないため、非常に重要な役割を担っています。

システムデータベースのリストアを検証する

システムデータベースにはさまざまな種類がありますが、今回は運用でリストアが必要になる可能性が高い3つのデータベースに絞って検証を行います。また、それぞれのデータベースが保持する構成情報が正しく復元できるかについても確認します。

対象データベース

• master
• model
• msdb

master

SQL Serverインスタンス全体の設定や情報を管理する最も重要なデータベースです。
ログイン情報やサーバ設定といったSQL Serverの構成情報に加え、各データベースの作成日時や格納場所などのメタデータを保持しています。今回はメモリ割り当てとログインを作成して設定値やオブジェクトが復元されるか確認します。

事前準備

「kazunari_saito」というログインを作成し、メモリ割り当てを8192MBに設定する

masterデータベースのバックアップを取得します。

USE [master]
GO
 
BACKUP DATABASE master
TO DISK='<backup destination path>'

メモリ割り当て値を10240MBに変更し、作成した「kazunari_saito」ログインを削除します。

masterデータベースをリストアする

SQL Server構成マネージャーを起動してSQL Serverに関連するサービスを全て停止します。

次に、コマンドプロンプトを管理者権限で起動し、SQL Serverのインストールディレクトリ内の「Binn」フォルダにある「sqlservr.exe」からSQL Serverをシングルユーザーモードで起動します。

> cd C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn
> sqlservr.exe -m

以下の画像のようになれば起動が成功しています。

コマンドプロンプトを閉じてはいけません。

masterデータベースをリストアする方法は主に2種類あります。
1つはローカル環境でのSQLCMDを使用する方法、もう1つはSQL Server Management Studio（SSMS）を使用する方法です。

【SQLCMDを使用する方法】
SQL Serverをシングルユーザーモードで起動した際のコマンドプロンプトとは別にもう一つコマンドプロンプトを管理者権限で起動し、SQLCMDで接続してリストアコマンドを実行します。

> sqlcmd -E -S <hostname>
1>RESTORE DATABASE master FROM DISK = '<backup file path>' WITH REPLACE
2>GO

masterデータベースが復元されるとSQL Serverは自動的にシャットダウンされます。

SQL Server Management Studio（SSMS）を使用する方法
SQL Serverをシングルユーザーモードで起動した後、SSMSを使用してクエリウィンドウを開きます。この際、シングルユーザーモードでは1セッションのみ接続可能なため、オブジェクトエクスプローラーなどの追加セッションを開かないようにしてください。
クエリウィンドウからリストアコマンドを実行します。

USE [master]
GO
 
RESTORE DATABASE master FROM DISK = '<backup file path>' WITH REPLACE
GO

masterデータベースが復元されるとSQL Serverは自動的にシャットダウンされます。

設定値やオブジェクトの確認

SQL Server構成マネージャーを起動してSQL Serverのサービスを起動します。

SQL Serverにログインして確認してみると「kazunari_saito」というログインが復元され、メモリ割り当ての設定値もバックアップ取得時の8192MBに戻っていることが確認できました。

model

ユーザーデータベースを作成するときのテンプレートとなるデータベースです。
今回は、modelデータベースにテンプレート用のテーブルを作成し、そのテーブルが復元されるかを検証します。また、新たにユーザーデータベースを作成した際に、そのテンプレートが正常に機能するかも併せて確認します。

事前準備

modelデータベースにテーブルを作成しデータを挿入しておきます。

USE [model]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[model_restore_test](
    [id] [bigint] NOT NULL
) ON [PRIMARY]
GO
 
INSERT INTO [dbo].[model_restore_test] VALUES (1)
 
SELECT * FROM [dbo].[model_restore_test]

modelデータベースのバックアップを取得します。

USE [master]
GO
 
BACKUP DATABASE model
TO DISK='<backup destination path>'

作成した「model_restore_test」テーブルにデータを追加で挿入します。

USE [model]
GO
 
INSERT INTO [dbo].[model_restore_test] VALUES (2)
 
SELECT * FROM [dbo].[model_restore_test]

modelデータベースをリストアする

SSMSを使用してSQL Serverにログインし、データベースをシングルユーザーモードに変更してリストア操作を実行します。

USE master
GO
 
ALTER DATABASE model SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 
RESTORE DATABASE model
FROM DISK = '<backup file path>'
WITH REPLACE
 
ALTER DATABASE model SET MULTI_USER
GO

テーブルの復元とテンプレート機能の正常性確認

「model_restore_test」テーブルは正常に復元され、バックアップ取得時のデータ件数1件の状態で復元されました。

新規に「testdb2」データベースを作成したところ、その配下に「model_restore_test」テーブルが作成され、1件のデータが挿入されている状態になっていました。このことから、テンプレートとしての機能が正常に動作していることが確認できました。

msdb

SQL Serverエージェントのジョブやスケジュール情報、バックアップ履歴などを管理しているデータベースです。今回は、レプリケーションの復元が可能かを検証します。レプリケーションの復元には、masdbに加え、distributionデータベースとユーザーデータベースの復元が必要です。レプリケーションの動作に必要なSQL Serverエージェントジョブはmsdbが管理しているため、レプリケーション復元の過程でジョブの復元が確認できます。

事前準備

テスト用データベース「testdb」を作成し、その中に「repl_test」テーブルを準備してデータを挿入します。

USE [testdb]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[repl_table](
    [id] [bigint] NOT NULL PRIMARY KEY
) ON [PRIMARY]
GO
 
INSERT INTO [dbo].[repl_table] VALUES (1)
 
SELECT * FROM [dbo].[repl_table]

レプリケーションを有効化し、別インスタンスのデータベースと同期を行いました。この操作により、distributionデータベースが作成され、レプリケーションに必要なSQL Serverエージェントジョブも生成されました。

msdb,distribution,testdbの各データベースのバックアップを取得します。

USE [master]
GO
 
BACKUP DATABASE msdb
TO DISK='<backup destination path>'
 
BACKUP DATABASE distribution
TO DISK='<backup destination path>'

BACKUP DATABASE testdb
TO DISK='<backup destination path>'

パブリケーションを削除します。この時点で同期が停止します。

ジョブも削除されていることが確認できます。

msdb,distribution,testdbの各データベースをリストアする

SSMSを使用してSQL Serverにログインし、各データベースをシングルユーザーモードに変更してリストア操作を実行します。

USE master
GO
 
ALTER DATABASE distribution SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 
RESTORE DATABASE distribution
FROM DISK = '<backup file path>'
WITH REPLACE
 
ALTER DATABASE distribution SET MULTI_USER
GO
 
ALTER DATABASE msdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 
RESTORE DATABASE msdb
FROM DISK = '<backup file path>'
WITH REPLACE
 
ALTER DATABASE msdb SET MULTI_USER
GO

ALTER DATABASE testdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
 
RESTORE DATABASE testdb
FROM DISK = '<backup file path>'
WITH REPLACE
 
ALTER DATABASE testdb SET MULTI_USER
GO

レプリケーションが復元されているか確認

削除したパブリケーションやサブスクリプションが復元されており、同様にSQL Serverエージェントジョブも正常に復元されていました。

復元直後の状態ではレプリケーションがエラーとなっており、ログリーダーエージェントとディストリビューションエージェントの手動起動が必要でした。その後、「testdb」データベースの「repl_table」テーブルにデータを挿入したところ、サブスクライバー側への同期が正常に行われることを確認できました。

まとめ

SQL Serverのシステムデータベースのリストアについて検証を行いました。システムデータベースのリストアは、DBサーバをゼロから構築する際に、バックアップからインスタンスの設定やオブジェクトを復元できるため、大規模な災害復旧をはじめ、さまざまな場面で活用できると考えています。しかし、通常の運用では操作する機会が少ないため、いざ使用する際に手順が明確になっていない可能性があります。そのため、緊急時に備えて、手順や準備を事前に整えておくことが重要だと感じました。