【注目記事】
・SEの転職体験談 | 受託開発から自社製品開発へ ・SIer勤務のSEがパソナキャリアに相談してみた ・SIer勤務のSEがマイナビエージェントに相談してみた
データベースには想定外の事態が起きて正常に動作しなくなり、最悪の場合データが破壊されてしまうリスクがあります。
そのような事態に備えて、定期的にデータをバックアップする必要があります。
この記事ではSQL Serverでデータをバックアップする方法と、バックアップしたデータを復元する方法を紹介します。
目次
前提知識
データベースとトランザクションログ
SQL Serverのバックアップ対象には「データベース」と「トランザクションログ」の2種類があります。
データベース
データベースは業務で使用するデータ(ex ECサイトであれば商品情報など)を格納するための場所です。
トランザクションログ
SQL Serverのようなデータベースアプリケーションは、データベースにデータを挿入したり、挿入したデータを更新したり、削除したりします。
それらのデータベースに対する命令をSQL Serverでは「操作」と呼びます。
そして、「操作」を取り消したり、再実行したりする場合に備えて「操作」記録しておくための場所がトランザクションログです。
完全バックアップと差分バックアップ
SQL Serverのバックアップには、完全バックアップと差分バックアップの2種類の方法があります。
完全バックアップ
指定したデータベース内のすべてのデータを対象にバックアップします。
差分バックアップ
最新の完全バックアップを基準にして、それ以降に変更されたデータのみを対象にしたバックアップです。
差分バックアップは完全バックアップを取得した後にしか使用できません。
そのため、はじめに完全バックアップを取得し、それ以降は差分バックアップを取得するという流れになります。
復旧モデル
SQL Serverの復旧モデルとは、トランザクションログをバックアップの対象に含めるかどうか、含める場合どのようにバックアップを取得するのかの設定です。
復旧モデルには単純、完全、一括の3種類があります。
単純復旧モデル
トランザクションログをバックアップ対象外にします。
そのため、障害が発生した場合は最新のバックアップ(完全 or 差分)までしか復旧できません。
完全復旧モデル
トランザクションログをバックアップ対象にします。
そのため、最新のバックアップ以降の操作までを含めて復旧できるため、基本的にデータベースに対して操作した内容が消えることはありません。
ただし、バックアップ前のトランザクションログ(ログの末尾)が何らかの理由で壊れている場合、該当部分のバックアップはできません。
一括復旧モデル
一括復旧モデルは、完全復旧モデルを補完するためのものです。
完全復旧モデルでは、大量のデータをインサートする際に、すべての操作をトランザクションログに書き込みますが、それによってトランザクションログが肥大化します。
肥大化を防ぐために、 一括復旧モデルでは大量のデータを操作する際に最低限のログだけを取得することで、トランザクションログのサイズを小さく保つことができます。
ただし、完全復旧モデルではバックアップを使って任意の時点に復旧することができますが、一括復旧モデルはバックアップ終了後の決まった時点にしか復旧できません。
(参考) 一括インポートで最小ログ記録を行うための前提条件2
復旧モデルの使い分け
最後にバックアップした時点までの復旧で問題なければ単純復旧モデル、バックアップ後の操作も含めて復旧したい場合は完全復旧モデルを選択します。
ただし、 bcp コマンドやBULK INSERT ステートメントなどで大量のデータをデータベースにインサートする際は、トランザクションログの肥大化を防ぐため、一時的に一括復旧モデルに設定を変更し、インサート終了後にもとの復旧モデルに戻します。
(参考) データの一括インポートの準備 (SQL Server)
バックアップ前にすること
復旧モデルを検討
要件に合わせてどの復旧モデルにするか検討します。
バックアップするスケジュールの検討
以下をどのタイミングで実施するのかを、要件に合わせて検討します。
- 完全バックアップの取得
- 差分バックアップの取得
- トランザクションログの取得(完全/一括復旧モデルの場合)
- バックアップファイルの削除
完全バックアップは毎日取得するのが理想的ですが、バックアップ対象の容量が大きい場合は週一程度にして、日時で差分バックアップを取得するなどの対応が必要です。
※ ずっと差分バックアップのみだと、復旧の際に反映すべきバックアップファイルの数が多くなり手間になってしまうため、定期的に完全バックアップを取り直す方が良いでしょう
トランザクションログは、バックアップを取得しないとログの切り捨てができずに肥大化してしまうため、1日に何度かバックアップを行いログの切り捨てができるようにします。
データベースが破損していないか確認
データベースは運用していくうちに破損する場合があります。
破損したデータベースをバックアップしても意味がないため、バックアップ前にデータベースが破損していないかコマンドを実行して確認します。
バックアップファイルの配置先を検討
バックアップしたファイルをどこに配置するかを検討します。
SQL Serverが稼働する端末と、バックアップしたファイルを配置する端末は必ず別にします。
SQL Serverが稼働する端末が故障した場合、バックアップしたファイルが使用できない恐れがあるためです。
また、配置先に選んだ端末のディスクに、充分な空き容量があることを確認する必要があります。
容量が足りない場合は、ディスクの容量を減らすか、バックアップファイルのサイズを小さくします。
バックアップの手順
完全バックアップの手順
(参考) データベースの完全バックアップの作成 (SQL Server)
※ どの復旧モデルも上記リンクの手順で実現できます
差分バックアップの手順
(参考) データベースの差分バックアップの作成 (SQL Server)
トランザクションログのバックアップ手順
(参考) トランザクション ログのバックアップ (SQL Server)
復元(リストア)の手順
完全復旧モデル / 一括復旧モデルの復旧手順
(参考) SQL Server データベースを特定の時点に復元する方法 (完全復旧モデル)
単純復旧モデルの復旧手順
(参考) データベースの差分バックアップの復元 (SQL Server)
スケジューリングの手順
SQL Serverでバックアップのスケジュールリングする場合、メンテナンスプランを利用します。
メンテナンスプランでは、指定した日付や間隔でデータベースの整合性チェックやバックアップを実行できます。
SQLスキルを高めるためのおススメ書籍
SQLのスキルを高めるには、学校の試験勉強と同じように多くの問題を解いてみるのが効率的です。
『スッキリわかるSQL入門 第2版 ドリル222問付き! (スッキリシリーズ)』は、SQLの基本的な内容の解説に加え、200問を超える問題が掲載されているので、SQL初心者が学習に使うのにおススメです。
実際、この書籍を新卒1年目の新人さんに2-3週間かけて取組んでもらったことがあり、書籍を読んだ後に簡単なSQLの改修を任せましたが、基本的な部分にはつまずかずに改修を進められました。
コメントを残す