-
Notifications
You must be signed in to change notification settings - Fork 0
MS_SQLServerBackup
- 戻る（バックアップのいろいろ、SQL Server）
- SQL Server のバックアップ
- SQL Server の障害復旧
SQL Serverのバックアップは、
システムのバックアップとデータのバックアップに分けて考える。
システムのバックアップとしては、システム・ドライブ丸ごとバックアップが
簡単ではある（システム・データベースはシステム・ドライブ上にある前提）。
もう少しきめ細やかにバックアップしたい場合、
システム・ドライブ丸ごとではなく、
システム・データベースを別途バックアップする。
例えば、システム・データベースの
msdbに
**「オンライン・バックアップのバックアップ・カタログ」**を持っているため、
これがなくなるとリストアが難しくなる。
システム設定は、基本的にシステム・データベースをリストアすれば同じになる。
ただし、すべての設定がシステム・データベースにあるとは限らないので、
OS、SQL Server インストール手順や、構築手順上の設定は控えておく。
補足（システム データベースの役割）: バックアップ対象の判断のため、
各システム DB の中身を押さえておく。
DB 内容 バックアップ masterログイン、サーバー構成、DB の所在 必須 msdbSQL Agent ジョブ、バックアップ履歴（カタログ） 必須 model新規 DB のテンプレート 変更したなら必須 tempdb一時領域。再起動で再作成される 不要 resourceシステム オブジェクト（読み取り専用） 不要 上位 2 つが特に重要である。
msdbを失うとバックアップ履歴が消え、リストア手順が分からなくなる
（バックアップ ファイル自体があっても、どれが完全でどれが差分か
追跡できなくなる）。
バックアップと復元の履歴に対する最新の変更情報の損失リスクを回避するために、
頻繁に msdb をバックアップしておく。
以下は、データだけではなく、システム丸ごと壊れた場合のリストア手順。
- 新しいマシンに SQL Server をインストールする。
- 旧マシンで取得したバックアップ ファイルをコピーする。
- ユーザー データベースをリストアする。
- 構成マネージャから SQL Server サービスを停止する（関連サービスも全て）。
- シングル ユーザー モードで SQL Server を起動する。
-
masterデータベースをリストアする。 - 構成マネージャから SQL Server サービスを起動する。
-
msdbデータベースをリストアする。 -
modelデータベースをリストアする。 - SQL Server サービスを再起動する。
- SQL Server Agent など、関連サービスを起動する。
移行メモ（正誤）: 元ページは手順 5 を
「シングルインスタンスモードで起動」と記しているが、
正しくは シングル ユーザー モード（sqlservr.exe -m）である。
masterは使用中のためオンラインでは復元できず、
このモードで起動する必要がある。
デタッチ後のファイル バックアップ。
- ファイルまたはファイル グループのバックアップ
- コピーのみのバックアップ
次の特徴がある。
- DB を停止し、ファイルを閉じる必要がない。
- バックアップ中にデータを変更できる。また、この変更分もバックアップされる。
- 制限されるアクティビティ
- DB の作成、変更
- DB の拡張処理、圧縮処理
- インデックスの作成
- その他、「トランザクション ログ」に記録されない操作
次の種類がある。
| 種類 | 内容 |
|---|---|
| 完全バックアップ | DB 全体 |
| 差分バックアップ | 最後の完全バックアップ以降の変更分 |
| トランザクション ログ バックアップ | 最後のログ バックアップ以降のログ（増分に相当） |
補足（差分と増分の違いに注意）: この 2 つは混同されやすい。
何からの差か リストアに要るもの 差分（Differential） 最後の「完全」から 完全 + 最新の差分 1 つだけ 増分（ログ バックアップ） 最後の「同種」から 完全 + 差分 + その後の全ログ 完全 ── 差分1 ── 差分2 ── 差分3 ← 復元は「完全 + 差分3」 完全 ── ログ1 ── ログ2 ── ログ3 ← 復元は「完全 + ログ1 + 2 + 3」差分は「回を重ねるほど大きくなるが、リストアが速い」、
ログは「小さいが、1 つ欠けるとそこから先を復元できない」。
実務では 週次の完全 + 日次の差分 + 数分ごとのログという
組み合わせがよく使われる。
復旧モデルによってトランザクション・ログの出力が変わり、
これによって選択可能なオンライン・バックアップ方式が変わる。
| 復旧モデル | ログ バックアップ | 復旧できる時点 |
|---|---|---|
| 単純 (Simple) | 不可 | 最後の完全 / 差分の時点まで |
| 完全 (Full) | 可 | 障害直前まで（ポイントインタイム） |
| 一括ログ (Bulk-logged) | 可（制限あり） | 一括操作中を除き障害直前まで |
補足（復旧モデルの選択が最重要）: バックアップ設計の起点はここである。
- 「単純」のまま本番運用しているというのが最も多い事故のもとで、
この場合前回のバックアップ以降のデータは復旧できない。- 逆に「完全」にしたのにログ バックアップを取っていないと、
トランザクション ログが際限なく肥大してディスクを食い潰す。
症状 原因 ログ ファイルが巨大化する 完全モデルなのにログ バックアップを取っていない 障害時に数時間分のデータが飛ぶ 単純モデルを使っていた 「完全モデルにする」と「ログ バックアップを定期実行する」は
必ずセットである。片方だけでは事故になる。
補足（取るだけでは意味がない）: バックアップ設計で最も忘れられるのが
復元の検証である。
観点 内容 RESTORE VERIFYONLYバックアップ ファイルが読めるか確認 CHECKSUMオプション取得時にページのチェックサムを検証 DBCC CHECKDB破損した DB をバックアップし続けていないか確認 復元訓練 別環境に実際に戻して RTO を測る 3 番目が特に重要で、破損したまま毎日バックアップを取り続け、
いざ戻したら壊れていたという事故が実際に起こる。
DBCC CHECKDBを定期実行し、
健全性を確認してからバックアップする運用が望ましい。
-
SQL Server データベースのバックアップと復元
https://learn.microsoft.com/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases -
システム データベースのバックアップと復元
https://learn.microsoft.com/sql/relational-databases/backup-restore/back-up-and-restore-of-system-databases-sql-server -
復旧モデル
https://learn.microsoft.com/sql/relational-databases/backup-restore/recovery-models-sql-server
Tags: 移行, データアクセス, SQL Server, バックアップ, 障害対応
このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。