SQL Server を リストアしよう

不幸は突然やってくる。。。SQL Server の DBバックアップを取得しても、リストアできないと意味ないですよね。ここではSQL Server 2019 のリストア方法について記載します。構築したら必ずテストして、リストア手順や運用フローの確認をしておきましょう。

バックアップの取得についてはこちらに記載しています。

SQL Server の DBバックアップをメンテナンスプランを利用して取得しよう

SQL Server のDBバックアップを取得する際、メンテナンスプランを利用すれば簡単に定期バックアップジョブを作成することが可能です。ここではSQL Server 2019 のメンテナンスプランの設定方法について記載します。

リストアの流れについて
システムDBの状態の記録
システムDBの再構築
システムDBのリストア
ユーザーDBのリストア

1.リストアの流れについて

リストア時の流れは以下となります。

1.システムDBの状態の記録 (構築後に実施する事前作業です。)
　※構築後に事前に実施しておいてください。

2.システムDBの再構築
　※システムDBを再構築する必要がない場合、この手順は不要です。

3.システムDBのリストア
　※システムDBをリストアする必要がない場合、この手順は不要です。

4.ユーザーDBのトランザクションログの取得
　※システムDBのリストアを行わない、かつ、ユーザーDBのトランザクションログが取得できる場合に行います。

5.ユーザーDBのリストア

2.システムDBの状態の記録

こちらの手順は、構築時に事前に実施しておいてください。システムDBの障害後には実施できません。

「システムDBの状態の記録」に関するMicrosoftの公式ページはこちら

システムデータベースを再構築する - SQL Server

master、model、msdb、または resource の各システムデータベースの損傷による問題を解決したり、既定のサーバーレベルの照合順序を変更したりするには、システムデータベースを再構築する必要があります。

1.サーバー全体のすべての構成値を記録する。

SQLCMDから以下のコマンドを実行する。

SELECT * FROM sys.configurations;

出力結果

出力結果例(サーバー全体のすべての構成値を出力)

C:\Users\administrator.TRUESYSTEMS>sqlcmd -o outfile1.txt
1> SELECT * FROM sys.configurations;
2> go
1> quit

outfile1 ダウンロード

2.SQL Server のインスタンスと現在の照合順序に適用されているすべての修正プログラムを記録する。

システムデータベースの再構築後にこれらの修正プログラムを再適用する必要があります。構築時に取得している場合、手順「3.システムDBの再構築」に進んでください。

SQLCMDから以下のコマンドを実行する。

SELECT  
SERVERPROPERTY('ProductVersion ') AS ProductVersion,  
SERVERPROPERTY('ProductLevel') AS ProductLevel,  
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,  
SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,  
SERVERPROPERTY('Collation') AS Collation;

出力結果

出力結果例(すべての修正プログラムを記録)

C:\Users\administrator.TRUESYSTEMS>sqlcmd -o outfile2.txt
1> SELECT
2> SERVERPROPERTY('ProductVersion ') AS ProductVersion,
3> SERVERPROPERTY('ProductLevel') AS ProductLevel,
4> SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
5> SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime,
6> SERVERPROPERTY('Collation') AS Collation;
7> go
1> quit

outfile2 ダウンロード

3.システムデータベースのすべてのデータとログファイルの現在の場所を記録する。

システムデータベースを再構築すると、すべてのシステムデータベースがそれぞれ元の場所にインストールされる為、システムデータベースのデータまたはログファイルを別の場所に移動していた場合は、そのファイルを再度移動する必要があります。

SQLCMDから以下のコマンドを実行する。

SELECT name, physical_name AS current_file_location  
FROM sys.master_files  
WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));

出力結果

出力結果例(データとログファイルの現在の場所の記録)

C:\Users\administrator.TRUESYSTEMS>sqlcmd -o outfile3.txt
1> SELECT name, physical_name AS current_file_location
2> FROM sys.master_files
3> WHERE database_id IN (DB_ID('master'), DB_ID('model'), DB_ID('msdb'), DB_ID('tempdb'));
4> go
1> quit

outfile3 ダウンロード

3.システムDBの再構築

SQL Serverが稼働しておらず、システムDBの再構築から実施が必要な場合、本手順を実施してください。ユーザーDBが稼働しており、ユーザーDBのリストアだけ実施する場合、手順「5.ユーザーDBのリストア」に進んでください。

「システムDBの再構築」に関するMicrosoftの公式ページはこちら

システムデータベースを再構築する - SQL Server

1.システムDBの再構築コマンドを実行する。

コマンドプロンプトで、以下のコマンドを実行する。

セットアップコマンドはシステム領域にインストールされています。

cd "C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019"

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=truesystems\administrator

出力結果

出力結果例(システムDB再構築)

C:\Users\administrator.TRUESYSTEMS>cd "C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019"

C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019>Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=truesystems\administrator
SQL Server 2019 では、インストール操作に関する情報と、他の使用状況とパフォーマンスのデータを Microsoft に送信し、製品の 品質向上に役立てます。SQL Server 2019 のデータ処理とプライバシー管理に関する詳細は プライバシーに関する声明 をご覧ください。

C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\SQL2019>

ログはこちらに出力されます。

C:\Program Files\Microsoft SQL Server\150\Setup Bootstrap\Logs

4.システムDBのリストア

「システムDBのリストア」に関するMicrosoftの公式ページはこちら

システムデータベースのバックアップと復元 - SQL Server

SQL Server では、サーバーインスタンスの運用に不可欠なシステムデータベースが管理されます。いくつかのシステムデータベースは、重大な更新が行われるたびにバックアップする必要があります。

1.システムDBのバックアップファイルが存在することを確認する。

今回使用しているSQL Server環境では、バックアップディレクトリに、”I:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup”を指定しています。

2.「Windowsマーク」-「Microsoft SQL Server 2019」 – 「SQL Server 2019 構成マネージャー」をクリックし、「SQL Server Configuration Manager」を起動する。

3.「SQL Server Configuration Manager」画面の左ペインから「SQL Server のサービス」をクリックし、右ペインにSQL Serverサービス一覧を表示する。

4.SQLサービスの稼働状況を確認し、サービスが起動していたら、サービスを選択し、右ボタンクリックで表示されるコンテキストメニューから「停止」をクリックし、サービスを停止する。

ここでは、「SQL Server (MSSQLSERVER)」、「SQL Server エージェント(MSSQLSERVER)」の2つのサービスが起動しているので、これらを停止します。

5.「SQL Server エージェント(MSSQLSERVER)」が起動している状態で、「SQL Server (MSSQLSERVER)」サービスを停止しようとすると、ダイアログ画面が開くので、「はい」ボタンをクリックする。

6.サービス「SQL Server (MSSQLSERVER)」の停止後、更新ボタンをクリックし、各サービスの状態を更新する。

7.サービス「SQL Server エージェント(MSSQLSERVER)」も停止していることが確認できる。

8.SQL Serverをシングルインスタンスモードで起動する。

コマンドプロンプトで、以下のコマンドを実行する。

今回使用しているSQL Server環境では、インスタンスルートディレクトリに、”E:\Program Files\Microsoft SQL Server”を指定しています。

cd /d "E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn"
sqlservr -m -c

出力結果

出力結果例(シングルインスタンスモードで起動)


C:\Users\administrator.TRUESYSTEMS>cd /d "E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn"

E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn>sqlservr -m -c
2022-10-05 11:55:19.31 Server      Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
        Sep 24 2019 13:48:23
        Copyright (C) 2019 Microsoft Corporation
        Developer Edition (64-bit) on Windows Server 2022 Standard Evaluation 10.0 <X64> (Build 20348: ) (Hypervisor)

2022-10-05 11:55:19.31 Server      UTC adjustment: 9:00
2022-10-05 11:55:19.31 Server      (c) Microsoft Corporation.
2022-10-05 11:55:19.31 Server      All rights reserved.
2022-10-05 11:55:19.31 Server      Server process ID is 4368.
2022-10-05 11:55:19.31 Server      System Manufacturer: 'VMware, Inc.', System Model: 'VMware7,1'.
2022-10-05 11:55:19.31 Server      Authentication mode is WINDOWS-ONLY.
2022-10-05 11:55:19.31 Server      Logging SQL Server messages in file 'G:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2022-10-05 11:55:19.31 Server      The service account is 'TRUESYSTEMS\administrator'. This is an informational message; no user action is required.
2022-10-05 11:55:19.31 Server      Registry startup parameters:
         -d G:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\master.mdf
         -e G:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG
         -l G:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2022-10-05 11:55:19.31 Server      Command Line Startup Parameters:
         -m
         -c
2022-10-05 11:55:19.31 Server      SQL Server detected 4 sockets with 1 cores per socket and 1 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2022-10-05 11:55:19.31 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2022-10-05 11:55:19.31 Server      Detected 4095 MB of RAM. This is an informational message; no user action is required.
2022-10-05 11:55:19.31 Server      Using conventional memory in the memory manager.
2022-10-05 11:55:19.31 Server      Page exclusion bitmap is enabled.
2022-10-05 11:55:19.37 Server      Buffer Pool: Allocating 524288 bytes for 360480 hashPages.
2022-10-05 11:55:19.39 Server      Default collation: Japanese_CI_AS (日本語 1041)
2022-10-05 11:55:19.45 Server      Buffer pool extension is already disabled. No action is necessary.
2022-10-05 11:55:19.47 Server      Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2022-10-05 11:55:19.49 Server      Query Store settings initialized with enabled = 1,
2022-10-05 11:55:19.49 Server      The maximum number of dedicated administrator connections for this instance is '1'
2022-10-05 11:55:19.49 Server      This instance of SQL Server last reported using a process ID of 4640 at 2022/10/05 11:43:43 (local) 2022/10/05 2:43:43 (UTC). This is an informational message only; no user action is required.
2022-10-05 11:55:19.49 Server      Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2022-10-05 11:55:19.49 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2022-10-05 11:55:19.50 Server      In-Memory OLTP initialized on lowend machine.
2022-10-05 11:55:19.51 Server      [INFO] Created Extended Events session 'hkenginexesession'

2022-10-05 11:55:19.51 Server      Database Instant File Initialization: 有効. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2022-10-05 11:55:19.51 Server      Total Log Writer threads: 2. This is an informational message; no user action is required.
2022-10-05 11:55:19.51 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2022-10-05 11:55:19.53 Server      clflushopt is selected for pmem flush operation.
2022-10-05 11:55:19.53 spid9s      Warning ******************
2022-10-05 11:55:19.53 spid9s      SQL Server started in single-user mode. This an informational message only. No user action is required.
2022-10-05 11:55:19.53 spid9s      Starting up database 'master'.
2022-10-05 11:55:19.61 Server      CLR version v4.0.30319 loaded.
2022-10-05 11:55:19.65 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2022-10-05 11:55:19.72 Server      Software Usage Metrics is enabled.
2022-10-05 11:55:19.73 spid9s      CHECKDB for database 'master' finished without errors on 2022-10-05 11:21:48.620 (local time). This is an informational message only; no user action is required.
2022-10-05 11:55:19.74 spid9s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2022-10-05 11:55:19.74 spid9s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
2022-10-05 11:55:19.76 spid9s      SQL Trace ID 1 was started by login "sa".
2022-10-05 11:55:19.76 spid9s      Server name is 'WIN2022SQL1'. This is an informational message only. No user action is required.
2022-10-05 11:55:19.77 spid9s      Always On Availability Groups was not started because the SQL Server instance is running in single-user mode.  This is an informational message.  No user action is required.
2022-10-05 11:55:19.78 spid11s     Starting up database 'mssqlsystemresource'.
2022-10-05 11:55:19.78 spid19s     Starting up database 'msdb'.
2022-10-05 11:55:19.78 spid20s     Starting up database 'AdventureWorks2019'.
2022-10-05 11:55:19.79 spid11s     The resource database build version is 15.00.2000. This is an informational message only. No user action is required.
2022-10-05 11:55:19.82 spid20s     Parallel redo is started for database 'AdventureWorks2019' with worker pool size [2].
2022-10-05 11:55:19.83 spid11s     Starting up database 'model'.
2022-10-05 11:55:19.86 spid17s     A self-generated certificate was successfully loaded for encryption.
2022-10-05 11:55:19.86 spid17s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2022-10-05 11:55:19.86 spid17s     Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2022-10-05 11:55:19.87 Server      Server is listening on [ ::1 <ipv6> 1434].
2022-10-05 11:55:19.87 spid19s     CHECKDB for database 'msdb' finished without errors on 2022-10-05 11:21:48.840 (local time). This is an informational message only; no user action is required.
2022-10-05 11:55:19.87 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2022-10-05 11:55:19.87 spid20s     CHECKDB for database 'AdventureWorks2019' finished without errors on 2022-10-05 11:21:49.080 (local time). This is an informational message only; no user action is required.
2022-10-05 11:55:19.87 Server      Dedicated admin connection support was established for listening locally on port 1434.
2022-10-05 11:55:19.87 spid17s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
2022-10-05 11:55:19.87 spid19s     Parallel redo is shutdown for database 'AdventureWorks2019' with worker pool size [2].
2022-10-05 11:55:19.87 Server      SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2022-10-05 11:55:19.87 spid11s     CHECKDB for database 'model' finished without errors on 2022-10-05 11:21:48.767 (local time). This is an informational message only; no user action is required.
2022-10-05 11:55:19.87 spid11s     Clearing tempdb database.
2022-10-05 11:55:19.88 Server      The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/WIN2022SQL1.ad.truesystems.jp ] for the SQL Server service.
2022-10-05 11:55:19.91 spid11s     Starting up database 'tempdb'.
2022-10-05 11:55:20.02 spid11s     The tempdb database has 4 data file(s).
2022-10-05 11:55:20.02 spid9s      Recovery is complete. This is an informational message only. No user action is required.

コマンド実行後、「Recovery is complete. This is an informational message only」というメッセージが出力されたら、シングルインスタンスモードでの起動完了です。

9.masterデータベースのリストア

手順「6.SQL Serverをシングルインスタンスモードで起動する。」で使用しているものとは別のコマンドプロンプトを起動し、以下のコマンドを実行すること

今回使用しているSQL Server環境では、インスタンスルートディレクトリに、”E:\Program Files\Microsoft SQL Server”を指定しています。

コマンドプロンプトで、以下のコマンドを実行する。

#コマンドプロンプト
cd /d "E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn"
sqlcmd -E

SQLCMDから以下のコマンドを実行する。

RESTORE DATABASE MASTER FROM DISK = '<masterデータベースのバックアップファイルパス>' WITH REPLACE

出力結果

出力結果例(masterデータベースのリストア)

C:\Users\administrator.TRUESYSTEMS>cd /d "E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn"

E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn>sqlcmd -E
1> RESTORE DATABASE MASTER FROM DISK = 'I:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\master_backup_2022_10_05_112149_9410101.bak' WITH REPLACE
2> go
データベース 'MASTER' の 512 ページ、ファイル 1 のファイル 'master' を処理しました。
データベース 'MASTER' の 2 ページ、ファイル 1 のファイル 'mastlog' を処理しました。
master データベースが正常に復元されました。SQL Server をシャットダウンしています。
SQL Server がこのプロセスを終了しています。

E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn>

masterデータベースが復元されると、シングルインスタンスモードで起動していたSQL Serverはシャットダウンされます。

8.「SQL Server Configuration Manager」画面において、サービス「SQL Server(MSSQLSERVER)」を選択し、右ボタンクリックで表示されるコンテキストメニューから「開始」をクリックし、サービスを開始する。

手順「7.masterデータベースのリストア」の実施後、シングルインスタンモードのSQL Serverは停止する為、本手順で起動させます。

9.msdb、modelデータベースのリストア

手順「6.SQL Serverをシングルインスタンスモードで起動する。」とは別のコマンドプロンプトを起動し、以下のコマンドを実行すること

今回使用しているSQL Server環境では、インスタンスルートディレクトリに、”E:\Program Files\Microsoft SQL Server”を指定しています。

コマンドプロンプトで、以下のコマンドを実行する。

#コマンドプロンプト
cd /d "E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn"
sqlcmd -E

SQLCMDから以下のコマンドを実行する。

RESTORE DATABASE MSDB FROM DISK = '<msdbデータベースのバックアップファイルパス>' WITH REPLACE
RESTORE DATABASE MODEL FROM DISK = '<modelデータベースのバックアップファイルパス>' WITH REPLACE

出力結果

出力結果例(mddb、modelデータベースのリストア)

E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn>sqlcmd -E
1> RESTORE DATABASE MSDB FROM DISK = 'I:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\msdb_backup_2022_10_05_112149_9410101.bak' WITH REPLACE
2> RESTORE DATABASE MODEL FROM DISK = 'I:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\model_backup_2022_10_05_112149_9410101.bak' WITH REPLACE
3> go
データベース 'MSDB' の 1824 ページ、ファイル 1 のファイル 'MSDBData' を処理しました。
データベース 'MSDB' の 2 ページ、ファイル 1 のファイル 'MSDBLog' を処理しました。
RESTORE DATABASE により 1826 ページが 0.062 秒間で正常に処理されました (230.027 MB/秒)。
データベース 'MODEL' の 352 ページ、ファイル 1 のファイル 'modeldev' を処理しました。
データベース 'MODEL' の 2 ページ、ファイル 1 のファイル 'modellog' を処理しました。
RESTORE DATABASE により 354 ページが 0.024 秒間で正常に処理されました (115.071 MB/秒)。
1> quit

E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn>

10.「SQL Server Configuration Manager」画面において、サービス「SQL Server(MSSQLSERVER)」を選択し、右ボタンクリックで表示されるコンテキストメニューから「再起動」をクリックし、サービスを再起動する。