SQL Server のバックアップ戦略、自動化と復元

SQL Serverレベル: 中級所要時間: 30～90 分

インストール済みの SQL Server インスタンス (Linux または Windows、単一インスタンス、FCI または AG レプリカ) のバックアップを構成し、バックアップから復旧します。バックアップ: 復旧モデルの選択、週次の完全 + 日次の差分 + n 分ごとのログバックアップ、COMPRESSION、CHECKSUM、証明書による暗号化、保持期間とスケジュール (SQL Server エージェントの T-SQL ジョブまたは Ola Hallengren、Express ではタスクスケジューラ / cron)、単発の COPY_ONLY バックアップ、バックアップの検証とチェーンの確認。復旧: 完全 / 差分 / ログチェーンの復元、STOPAT による特定時点への復元 (PITR)、マークされたトランザクション、WITH MOVE による別名または別サーバーへの復元、データファイル喪失時の tail-log + 復元、ページ復元、master / msdb / model の復元、ログインのマッピング (orphaned user)、暗号化バックアップ用の証明書の読み込み。

目的をシナリオカードから選択してください。エラー番号 (例: 824、4305、33111、18456) をカタログ検索に入力すると、該当するシナリオに直接移動できます。テーブルやクエリ結果をファイルにエクスポートする場合 (バックアップではなくデータ移動) は bcp によるエクスポートを参照してください。

構成: 1 台のサーバー、フェールオーバークラスターインスタンスまたは Always On AG レプリカ (別サーバーへの復元: 移行元 + 移行先)

シナリオ

バックアップ戦略を構成する (スケジュールバックアップ + 保持期間) — 新しいサーバー、または定期バックアップのないデータベース: 完全 / 差分 / ログのジョブ、保持期間、監視を構成します。
単発のバックアップを取得する (例: 変更前の COPY_ONLY) — 変更、パッチ適用、アップグレードの前、またはテスト環境へのコピー用の単発バックアップです。COPY_ONLY はチェーンを壊しません。
バックアップを検証する (履歴、VERIFYONLY、テスト復元) — バックアップが実際に復元できることを証明します: バックアップ履歴、VERIFYONLY、HEADERONLY / FILELISTONLY、一時データベースへのテスト復元 + CHECKDB。
バックアップチェーンは健全か (LSN の欠落、ファイルの欠落) — 復元の前、または定期監査として: msdb の履歴からチェーンを再構成し、LSN の欠落、ディスク上に存在しないファイル、途中に割り込んだバックアップを検出します。何も変更しません。
データベースをバックアップから復元する (チェーン、STOPAT / STOPATMARK、MOVE / REPLACE、別サーバー) — データが削除された、または誤って更新された、データベースのコピーが必要、あるいは別サーバーへ移行する。インスタンスは稼働中です。完全 + 差分 + ログチェーン、特定の時点またはマークされたトランザクション、別の名前 / ディレクトリ、tail-log による直前の時点までの復元。
データベースが開かない: データファイルの喪失または破損 (tail-log + 復元) — データベースが RECOVERY_PENDING または SUSPECT で、.mdf/.ndf を読み取れないがインスタンスは稼働中: まず NO_TRUNCATE で tail-log バックアップを取得し、その後直前の時点まで復元します。作業が終わるまでデータベースは使用できません。
一部のページが破損 (Msg 823 / 824): ページ復元 — クエリまたは CHECKDB が特定のページでエラーになる (msdb.dbo.suspect_pages): 破損したページだけをバックアップから復元します。Enterprise/Developer ではデータベースはオンラインのまま、Standard/Express では短時間オフラインになります。
master データベースを復元する (シングルユーザーモード) — master の破損、またはログイン / リンクサーバー / サーバー設定の喪失: インスタンスをシングルユーザーモード (-m) で起動して master を復元すると、SQL Server は自動的に停止します。ダウンタイムが必要です。インスタンスがまったく起動しない場合は、先にシステムデータベースを再構築します。
msdb または model データベースを復元する — ジョブ、バックアップ履歴、データベースメール (msdb)、または新規データベースのテンプレート (model) が破損 / 喪失: 同じビルドで取得したバックアップから REPLACE で復元します。エージェントは短時間停止します。
復元後にユーザーが接続できない (orphaned user、ログインのマッピング) — データベースを別サーバーへ移行または復元し、アプリケーションが 'Login failed' になる: ログインを SID とパスワードハッシュごと移行元から転送し、残りのユーザーをログインにマッピングします。データベースはオンラインのままです。
暗号化されたバックアップを復元できない (証明書がない、Msg 33111) — バックアップ (または TDE データベースのバックアップ) が証明書で暗号化されており、このサーバーに証明書がない: 拇印 (thumbprint) を特定し、証明書を秘密キーとともに読み込み、バックアップを検証します。既存のデータベースには影響しません。

開始前の確認

RPO (許容できるデータ損失) と RTO (許容できるダウンタイム) を業務部門と書面で合意した。選択した計画: …データ損失は最大 … 分…最後の完全/差分バックアップへの復帰 (SIMPLE: 最大 1 日分のデータ損失)…。復元時間は最大のデータベースのテスト復元で測定する。
SQL Server が稼働しており、mssql-tools18 (sqlcmd) がインストール済みで、… は sysadmin である。スクリプトを実行する前に、パスワードを画面に表示せずに変数へ読み込む: read -rsp 'パスワード: ' SQLCMDPASSWORD; export SQLCMDPASSWORD、続いて sudo --preserve-env=SQLCMDPASSWORD bash <script>.sh。
PowerShell を管理者として開いた (スタート → "PowerShell" → 右クリック → Run as administrator、タイトルに Administrator: と表示される)。SQL Server で sysadmin の Windows アカウントを使用し、sqlcmd は PATH にある。
バックアップディレクトリ (…) はデータディスクとは別のディスク上にあり、少なくとも 1 週間分のバックアップに足りる空き領域がある (目安: 使用データサイズ × 2 + 日次変更量 × 日数。圧縮すると通常 3～5 分の 1)。
バックアップディレクトリ (…) はデータディスクとは別のディスク上にあり、少なくとも 1 週間分のバックアップに足りる空き領域がある (目安: 使用データサイズ × 2 + 日次変更量 × 日数。圧縮すると通常 3～5 分の 1)。
データベースのサイズを把握しており、バックアップウィンドウ (完全バックアップにかかる時間) が業務時間と重ならない。
バックアップをサーバー外 (別サーバー、NAS、オブジェクトストレージ、バックアップソフトウェア) へコピーする方法を決めた。同じディスク上にあるバックアップは、ディスクやサーバーを失えば役に立たない。
復元先の SQL Server のバージョンは、バックアップを取得したバージョンと同じかそれより新しい。新しいバージョンで取得したバックアップは古いバージョンに復元できない (例: 2022 → 2019 は不可)。
アプリケーションチームに連絡し、アプリケーションを停止した: 上書き復元するデータベースへの接続はすべて切断され、復元中はデータベースを使用できない。
移行先にデータ (…) とログ (…) のディレクトリがあり、所有者は mssql で、復元するデータベースのファイルサイズ分の空き領域がある (バックアップのサイズではなく、FILELISTONLY の Size の値)。
移行先にデータ (…) とログ (…) のディレクトリがあり、SQL Server のサービスアカウントに書き込み権限があり、復元するデータベースのファイルサイズ分の空き領域がある。
移行先サーバー (…) から移行元 (…) へ …SSH (root または sudo 権限を持つユーザー) で…SMB 管理共有 (…) で…アクセスでき、バックアップファイルをコピーできる。

ガイドのフェーズ

現在の状態 — データベースと最近のバックアップを一覧表示する
バックアップ戦略 — バックアップディレクトリを準備する · バックアップディレクトリを準備する · 復旧モデルを設定する (…) · AG のバックアップ優先設定を構成する (…) · 優先レプリカを確認する · 証明書を作成してバックアップする (暗号化) …
単発バックアップ — …COPY_ONLY の完全バックアップを取得する…完全バックアップを取得する…差分バックアップを取得する…ログバックアップを取得する… (…) · バックアップ履歴を確認する (msdb.dbo.backupset)
復元 — バックアップ履歴から復元チェーンを特定する · バックアップファイルを移行先サーバーへコピーする · バックアップファイルを移行先サーバーへコピーする · 証明書を移行先サーバーに読み込む · 証明書を移行先サーバーに読み込む · バックアップファイルを調べる (VERIFYONLY、HEADERONLY、FILELISTONLY) …
バックアップの検証 — バックアップ履歴を確認する (msdb.dbo.backupset) · バックアップファイルを調べる (VERIFYONLY、HEADERONLY、FILELISTONLY) · テスト復元を行い、DBCC CHECKDB を実行する
バックアップチェーンの確認 — 診断: チェーンの状態 (データベース + msdb の履歴) · バックアップファイルはディスク上にあるか · バックアップファイルはディスク上にあるか · チェーン内のすべてのバックアップを検証する (VERIFYONLY) · 事後: チェーンが壊れていれば新しい完全バックアップを取得する
データファイルの喪失/破損: tail-log + 復元 — 診断: データベースとファイルの状態 · 診断: ファイルはディスク上にあるか、ログファイルは健全か · 診断: ファイルはディスク上にあるか、ログファイルは健全か · セーフティネット: 残っているファイルをコピーする · セーフティネット: 残っているファイルをコピーする · tail-log バックアップを取得する (NO_TRUNCATE) …
ページ復元 (破損ページ) — 診断: 破損ページを特定する (suspect_pages、CHECKDB) · 診断: ページはどのテーブル/インデックスに属するか · セーフティネット: ログバックアップを取得し、ログバックアップジョブを停止する · msdb の履歴から復元の順序を組み立てる (PAGE) · tail-log バックアップを取得する (NORECOVERY): データベースはオフラインになる · 破損ページを復元し、ログバックアップを適用する (NORECOVERY) …
master restore — 診断: バックアップファイル、バージョン、失われるもの · 診断: SQL Server が起動しない原因 · 診断: SQL Server が起動しない原因 · システムデータベースを再構築する (master が起動しない) · システムデータベースを再構築する (master が起動しない) · SQL Server をシングルユーザーモードで起動する …
msdb / model の復元 — 診断: 状態、バージョン、バックアップファイル · セーフティネット: 現在の … データベースのバックアップ · SQL Server エージェントを停止する · … データベースを復元する (REPLACE) · SQL Server エージェントを起動する · 検証
ログインマッピング (orphaned user) — 診断: 孤立ユーザーを特定する (移行先) · 移行元でログインのスクリプトを生成する (SID + パスワードハッシュ) · 移行元でログインのスクリプトを生成する (SID + パスワードハッシュ) · ログインのスクリプトを移行先へコピーして実行する · ログインのスクリプトを移行先へコピーして実行する · 残りのユーザーをログインにマッピングする (ALTER USER WITH LOGIN) …
暗号化されたバックアップ: 証明書を読み込む — 診断: このサーバーの証明書と master key · 診断: 証明書ファイルの拇印 · 診断: 証明書ファイルの拇印 · 証明書を秘密キーとともに読み込む · 証明書を秘密キーとともに読み込む · 検証: バックアップを読み取れるようになった …

環境に合わせた手順プランを開く →