データベースのバックアップリストアの仕組みについて

質問

【環境】
SQL Server 2008 R2 Express Edition (x64)

データベースの勉強をしています。
データベースの場合、データベースを管理しているのはOSではなく、DBMSのため、ユーザーデータベースを戻しただけでは、戻らないとに認識しています。
EXCELのファイルのバックアップ／リストアは、単にEXCELファイルをバックアップしてリストアすればいいのは分かりますが、データベースの場合のバックアップ／リストアについての仕組みについてよく理解できていません。
理解する上で、いろんなパターンのバックアップ／リストアを試そうと思っており、以下の件につきましてご教示を頂けますようお願い致します。

(1)　障害が発生した場合を想定してのデータベースのバックアップですが、ユーザーデータベースだけではなく、masterデータベースのバックアップも必要という認識であっておりますでしょうか。
masterデータベースにはユーザーデータベースの場所、ユーザー情報、ユーザーのロール情報が含まれているため、バックアップしたユーザーデータベースだけリストアしても復旧できないということでよいでしょうか。
masterデータベースが損傷していなければ、バックアップしたユーザーデータベースだけリストアすれば、復旧できる。

(2)　ユーザーデータベースが10個あり、そのうち特定なユーザーデータベース1個のバックアップおよびリストアテストをする手順については、下記でよろしいでしょうか。
テスト後は、次のテストパターンを学習するため、元の状態に戻したいと考えています。

<手順>
1.システムデータベース、必要なユーザーデータベース1個をのバックアップを行う。
2.ユーザーデータベース10個をデタッチする。
3.システムデータベースをデタッチする。
4.デタッチしたシステムおよびユーザーデータベースを別の場所に退避する。
5.システムデータベース、特定のユーザーデータベース1個をリストアする。
6.リストアしたユーザーデータベースが問題ないことを確認する。
7.リストアしたユーザーデータベース1個をデタッチする。
8.リストアしたシステムデータベースをデタッチする。
9.退避したシステムデータベース、ユーザーデータベースを元の場所に戻す。
10.システムデータベースをアタッチする。
11.ユーザーデータベース10個をアタッチする。

回答No.4

IDii24 · ベストアンサー

(1) DBのバックアップ時にログファイルも含めるというオプションがある。コミットとは関係ありません。含めない場合はベットログファイルをバックアップする必要がある。
(2).pubsは間違え昔のサンプルDB。modelでよいです。
(3) 排他制御というのは別の概念でありバックアップとは関係ありません。バックアップコマンドでは避けているだけでありその意味でログファイルは必要ということ。
<手順>
通常のシステムで10個もDBがあるというシュチュエーションが良く分かりませんが本当に10個あるならそうです。
システムDBは構成情報と統計情報を格納しているだけなので、そこまでリアルタイム性を求めません。あとから再統計コマンドを流しても問題ありませんが、まあこれでもOKです。
リアルタイム性を求めるならログファイルのほうが大事であり。さらにログファイルを順番に戻す必要があります。

補足

ご教示頂きありがとうございます。

DBのバックアップ時にログファイルも含めるというオプションですが、完全バックアップということでしょうか。完全バックアップを行った場合、トランザクションログは含まれませんでしょうか。

Microsoft Press SQL Server2008(日経BP)を見ると障害発生が2016/2/28に発生して、障害発生直前の2016/2/27時点に戻すには　(1)　→　(2)　→　(3)　→　(4)　の順番とのことですが、完全バックアップを毎日行ってる場合、障害発生直前の完全バックアップがあればいいという認識であっていますでしょうか。

下記の場合のみバックアップしたトランザクションログを順番に戻す必要があるということでしょうか。

今回の手順では、完全バックアップでバックアップを考えています。
-------------------------------
完全バックアップ 2016/2/22 (1)
ログバックアップ 2016/2/23
ログバックアップ 2016/2/24
差分バックアップ 2016/2/25　(2)
ログバックアップ 2016/2/26　(3)
ログバックアップ 2016/2/27　(4)

障害発生　　　　 2016/2/28
------------------------------

日付毎にデータベースが作成されていき、現在そのデータベースが10個あるということです。
---------------
DB1_20160218
DB1_20160219
DB1_20160220
DB1_20160221
DB1_20160222
DB1_20160223
DB1_20160224
DB1_20160225
DB1_20160226
DB1_20160227
--------------

回答No.6

IDii24

>前日分のデータは別のシステムに送信しているので、一日分あれば基本的には問題はないです。
ということは前日に戻るにはこのDBを使えばよいだけでは？まあ前日のバックアップを戻してログを順番に戻す。これでログを取得した時間までは戻せるってことです。
masterDBは構成情報です。システムの権限などの構成を戻す必要がある場合。統計を戻す必要がある場合（大きな障害では殆どそうです）どうしても必要です。簡単なDBならいりません。
そもそもDBのバックアップというのは確実に戻るかと言われれば戻すのは難しいです。構成や権限情報が複雑になればなるほど戻しにくい。得にアタッチは失敗する可能性が大きい。これはmasterDBに格納されている権限情報などに相違ができるからです。
だからOSごと取得してしまう方法も結構一般的です。通常バックアップファイルでさえも権限で制御されているものです。持ち出されて読み取られないように。つまり簡単に他のシステムにアタッチされては困るものなのです。だからmasterが必要なのですから。

お礼

ありがとうございます。

簡単なDBかについてはあるメーカーのアプリケーションで使用されているのでわからないですが、ユーザーアカウントも5人程度でDBも毎日作成されるのでmasterデータベースが持っている構成情報は変わります。

EXECLなどのファイルのバックアップであれば、消してしまったファイルだけ戻せばいいのでまだましですが・・・。
DBの場合は、masterデータベースにユーザー、ロール、データベースの構成情報を以前と同じように復旧させないと
アプリケーションからユーザーデータベースにアクセスできないという事態になるので、難しいと思います。

一応、OS、SQL Server、そのデータベースをSnapshotで取得しているようです。
Snapshotであれば、1時間程で戻せるので・・・。
ただ、1日、1回なのでSnapshotを取得して、次のSnapshot取得までの間で障害があるとその間のデータは戻せませんが。

masterを他のシステムにアタッチはできないので、その点については大丈夫です。
Aシステム、Bシステムを管理していてA,BシステムにSQL Serverがインストールされていれば、Aシステムのmasterを
Bシステムにアタッチということもできますが、管理しているのはAシステムのみでBシステムは別の会社が管理しているので、Bシステムのアカウント情報は一切知りませんので、masterをアタッチする以前の問題でログインすらできません。

管理しているAシステムの別サーバー、誤ってにアタッチしてしまうということはあるかもしれませんが・・・。

回答No.5

IDii24

まずまだ概念が・・・・。
バックアップは基本データーが動いている頻度に基づいてとるもの。完全バックアップを日曜に取得した場合月から土は差分バックアップでとる。さらにログのバックアップはシステムが稼働している日中に毎日取得が基本。深夜に通常のバックアップがあるなら、9時から5時までは1時間置きとか2時間置きとか使用頻度で決める。
また深夜にバックアップを取るときはログのバックアップは捨ててよい。ログも切り捨ててよい。
水曜の12時に障害発生なら日曜を戻して、月、火の差分を戻してさらに9時、10時、11時のログを順番に戻す。これで水曜の11時まで戻るという仕組み。1時間ごとにログをバックアップした場合。つまり直前まで戻す必要がそのシステムにどれくらいあるかということ。11時から12時まではユーザーがやったことを自分で再実行してもらう必要があるので、それができるならそれでOK。それができないシステムであればそもそもバックアップ自体が無意味。銀行ATMなど。これらは冗長化して無停止のシステムとしている。日時のバックアップだけをとる。
そして毎日DBができるってのは1日前のはもう更新しないという意味では？であればこれはスナップショットでありバックアップなんか1日分だけでよいと思いますが？

補足

ありがとうございます。

仮に毎日9時～20時までしかデータが動いていない状況で、毎日23時に完全バックアップしか行っていない場合において3/2の19時に障害があった場合、3/1の20時までのデータが23時の完全バックアップにおいて取得されているのでその時点までは復旧できる。

仮に障害発生の1時間前まで復旧させたいということならば、23時の完全バックアップ取得後、トランザクションログのバックアップを9時から1時間おきにバックアップするなどすればよいということであっておりますでしょうか。
-------------------------------------
2016/2/29 23時の完全バックアップ成功
2016/3/1 23時の完全バックアップ成功
2016/3/2 19時に障害発生
-------------------------------------

毎日DBが出来上がり、前日分は更新されません。また、前日分のデータは別のシステムに送信しているので、一日分あれば基本的には問題はないです。

質問させて頂きましたmasterデータベースのバックアップですが、masterデータベースがなくてもユーザーデータベースは復旧できるようですので、masterのバックアップは必ず必要ではないようです。

「ひと目でわかる　Microsoft SQL Server 2008(Microsoft Press)」に「システムデータベースも、通常のデータベースと同じようにバックアップする必要があります」と記載されていたので、バックアップしないとユーザーデータベースは復旧ができないと思っておりました。
-----------------------------------------------------------------------------------------
システム データベースの再構築

https://msdn.microsoft.com/ja-jp/library/dd207003(v=sql.105).aspx

再構築後の作業

バックアップが存在しないか、復元されたバックアップが最新のものでない場合は、
存在しないエントリを再作成します。たとえば、ユーザー データベース、バックアップ デバイス、
SQL Server ログイン、エンドポイントなどの存在しないエントリをすべて再作成します。
エントリを再作成する場合、エントリの作成時と同じスクリプトを実行してください。
-------------------------------------------------------------------------------------------

回答No.3

原沢 信道（@nharasawa）

基本的には問題に出たデーターベース以外の正常なデータベースはmasterも含め何もする必要はありません。バックアップはデータベース本体と更新ログの２種類があり、前回の本体バアクアップからそれ以降のログのバックアップ（していない場合は不要）とクラッシュ時点のログ（RDBMSが使用中のログファイル）が有ればクラッシュ直前まで復元出来ます。復元は現時点でRDBMSが持っている該当データベースのログをバックアップしてから、本体バックアップをリストアし、それ以降のログバックアップ分を古い順にリストアし、最後に先ほどバックアップしたログをリストアして完了です。

補足

ありがとうございます。

「ひと目でわかる　Microsoft SQL Server 2008(Microsoft Press)」に「システムデータベースも、通常のデータベースと同じようにバックアップする必要があります」と記載されていたので、バックアップしないとユーザーデータベースは復旧ができないと思っておりました。

masterデータベースが、なくてもユーザーデータベースが戻せるのならば、masterデータベースをバックアップしなくてもよいということになりますが、「システムデータベースも、通常のデータベースと同じようにバックアップする必要があります」と記載している理由に疑問が残っています。
-----------------------------------------------------------------------------------------
システム データベースの再構築

https://msdn.microsoft.com/ja-jp/library/dd207003(v=sql.105).aspx

再構築後の作業

バックアップが存在しないか、復元されたバックアップが最新のものでない場合は、
存在しないエントリを再作成します。たとえば、ユーザー データベース、バックアップ デバイス、
SQL Server ログイン、エンドポイントなどの存在しないエントリをすべて再作成します。
エントリを再作成する場合、エントリの作成時と同じスクリプトを実行してください。
-------------------------------------------------------------------------------------------

回答No.2

IDii24

追加です。
ファイルだけをバックアップするという方法は実は一般的ではありません。まあ戻せるのでOKと言えばOK。
データーベースファイルというのは初期状態で容量を決めます。例えば初めから１０G、１Tとか決めて作成されます。そうするとたとえ１０Mしかない情報でも１０Gとしてバックアップされてしまい無駄になりますし、時間もかかります。ですので通常はデーターだけのバックアップを取得し戻すという処理になります。ただし数テラも情報がある場合はシステムまるまるバックアップした方が確実という事もあります。つまりこれもシステム次第でとり方を変える必要があるということです。
アタッチなどでファイルをもどすならmasterは危険です。master自体も動いていた場合戻らなくなります。つまりデタッチ・アタッチは開発などのテスト環境を作るなどに適しているという事です。まあ普通はやらないと思いますよ。
RDBMSの管理は業務と結びついています。大きいシステムならトランザクションテーブルごとにユーザ＝ファイルが作られている場合もあります。格納されている場所もマスター情報と違うドライブだったりします。そのほうが効率が良いから設計段階でそうしているので、とにかくバックアップはDBAの指示に従う。それだけです。

補足

ご教示頂きありがとうございます。

(1) データベースのバックアップの際にトランザクションログがコミットされるのため、データベースの バックアップで取得するのはデータベース(.mdf)だけでトランザクションログ(.ldf）のバックアップは不要という認識であっていますでしょうか。

(2).システムデータベースには、master 、model、msdb、tempdbがあるようですが、pubというシステムデータベースもあるのでしょうか。

(3) デタッチの場合、排他制御が行われないのでデータベースに書き込みがされている最中にデタッチをするとデータベースが損傷する危険性があり、データベースのバックアップならば、排他制御が行われるため、大丈夫ということでしょうか。

その認識であっている場合、デタッチ／アタッチで行っていた手順は、以下のようになりますでしょうか。

<手順>
1.システムデータベース、ユーザーデータベースを10個のバックアップを行う。
2.システムデータベース、必要なユーザーデータベース1個をのバックアップを行う。
3.システムデータベース、必要なユーザーデータベース1個をのリストアを行う。
4.動作確認を行う。
5.動作確認後、元の状態に戻すため、システムデータベース、ユーザーデータベースを10個のリストアを行う。

CA社のARCServe、Symantec社のBackUpExecなどのバックアップソフトは使用せずに、SQL Serverに搭載されているsqlcmdコマンドまたはosqlユーティリティを使用してバックアップを考えております。

回答No.1

IDii24

理屈を理解しないと。理屈さえわかれば簡単な話しです。
お使いのDBMSはMS SQLServerのようですがどうでしょうか？
RDBというのはマスターとトランザクションのテーブルとに分かれます。その場合バックアップを取ったところで、動いている情報はその時点のものでは変化してしまっています。
詳しくは夜間にバックアップをしても日中に障害があればそれは役に立たないものであり、それだけでは業務は続けられません。
そのためにユーザーがやった処理。つまりバックアップ以降にした更新についてトランザクションログという形で保管しています。ログファイルはユーザーDBごとにあります。つまりこれを次のDBバックアップまで保持する必要があります。
日中にこのファイルを頻繁にバックアップし、避けることで数時間前まで戻せるということになります。
https://msdn.microsoft.com/ja-jp/library/ms177446(v=sql.120).aspx
このファイルはDBのバックアップで切り捨て、日中もログファイルのバージョンで保管するのが良いと思います。その辺はそのDBの更新の頻度によります。それゆえにDB管理者はシステムを理解し、最適なパフォーマンスを考える必要があるのです。
ログファイルは切り捨てないと肥大し容量を圧迫しますので意識的に切り捨てる必要がありますが、うろ覚えですがMSSQLではDBのバックアップと同時に切り捨てられたと思います。Oracleではこれが無かったと思いますが。
またmasterは構成DBなので必要です。たしかPubも必要だったと思います。
さらにデタッチとアタッチはバックアップの方法によっては必要ですが、通常はRDB専用のバックアップソフトで行えばその必要はありません。ファイルはソフトが勝手にバックアップしますし、モノによってログファイルなどもそこで戻せるようなものまであります。