【図で分かる！】SQL Server 活用入門　バックアップ

データベースを使い始めると次第に大切なデータが蓄積されていきます。

これらのデータを失うことは会社などの組織にとっては経済的な損失に等しい影響があります。

このため、データベースの運用者は必ずバックアップを取る必要があります。

SQL Server ではバックアップはコマンド一つで簡単に作成できますので、多くの人にとってハードルは低いはずです。

しかし、ただ単にバックアップを取る操作をするだけではデータベース上のデータを守る「仕組み」にはならないので、きちんと「仕組み」を作る必要があります。

この記事ではバックアップの基本的知識から、「仕組み」の実装までを解説しますので、是非参考にしてみて下さい。

バックアップの必要性－様々なデータ破壊リスク

通常の運用ではデータは簡単には破損しません。しかし、万一データを失うとその影響はかなり大きなものになってしまいます。

自動車事故への「保険」と同様 にバックアップの「仕組み」を構築しておくことで、安心してデータベースを運用することができます。

データ破損のリスクは様々ありますが、例えば次のようなものがあります。

ユーザーの操作ミス

データベースはデータを高速に処理できるため、コマンド一つで大量のデータを一瞬で処理できます。

これは裏を返すと、コマンド一つで大量のデータを破壊してしまうリスクがあるということです。

データベースを運用する中でこのような操作ミスが起きないとも限りません。

このような操作ミスは やってしまった直後に気付く ケースが多いです。

その他には、ユーザーが勘違いして一定の期間データを誤って操作し続けてしまうというケースもあります。

これは 静かに時間をかけてデータの破壊が進む ケースになります。

コンピューターの故障

コンピューターも機械であるため、故障するリスクというのは一定程度存在します。

特にハードディスクが突然壊れてデータが取り出せなくなるというような事態は非常に現実的なリスクです。

最近では仮想サーバーが普及し、コンピューターの故障のリスクはより低くなりつつありますが、それでも仮想サーバーの運用ミスによりデータが失われるというリスクには備えておく必要があります。

バックアップは気軽に復元できる

バックアップを取っても、それを活用できなければ意味がありません。

バックアップは運用中のデータを 上書きすることなく別の名称で復元することができる ので、運用中のデータと比較しながら慎重にデータ復元操作を行うことができます。

このため、とにかくバックアップを取っておけば安心できるという訳です。

バックアップを計画する

バックアップの操作は簡単ですが、何時・誰が・どのように バックアップすれば良いのでしょうか？

ここがしっかり押さえられていないいと無計画なバックアップとなり、いざという時に役に立たない ものになってしまいます。

ですから、ここでバックアップの考え方を整理しておきます。

リスクに対応した対策

バックアップは リスクへの備え、つまり「保険」です。適切に設定することでその効果が発揮されます。

また、リスクへの備えとして高いレベルのバックアップをするということは、それに比例して作業の手間や、ディスク容量などの「コスト」がかかるということを良く理解しておく必要があります。

先ほどご紹介したデータ破損リスクに対しての備えとして例えば次のようなことが必要となります。

最低1日1回のバックアップ

ユーザーによるデータ破壊 や サーバーコンピュータの破損 はある日突然やってきます。

この場合、なるべく最新のデータを取り戻す必要があります。

バックアップは取っていたもののそれが１月前のものだった場合、１ヶ月分の関係者の努力は水の泡となってしまいます。

この影響をなるべく小さくするには最低でも１日１回のバックアップを残しておくことをオススメします。

自動でバックアップ

バックアップの作業を人任せにすると、その人がうっかり忘れてしまうという新たなリスクを取り込むことになってしまいます。

ですから、放っておいても自動でバックアップが実行されるような「仕組み」を構築する必要があります。

保管先は別のコンピューター

せっかくバックアップを取っていても、 サーバーコンピュータが破損 した場合、バックアップファイルも同時に失われてしまってバックアップの意味が無くなってしまいます。

ですから、取得したバックアップは必ず別のコンピュータに移して保管しましょう。

バックアップの世代管理

バックアップ取得は簡単ですが、取得する度にファイルの容量が大きくなり、１ヶ月もすればあっという間に何ギガバイトという巨大ファイルになってしまうということにもなりかねません。

このため、どのようにバックアップを残しておくかという「世代管理」が必要になります。

例えば毎日バックアップを残すが、1ヶ月以上前のバックアップは残さないなど、取得したバックアップを管理することが「世代管理」になります。

SQL Management Studio でのバックアップと復元操作

自動バックアップの「仕組み」を構築する前に マニュアルでのバックアップと復元の操作 を身に付けておきましょう。

そうすることで、いざという時に自分で対処することができるので安心です。

そもそも、バックアップは万一の事態のためのものなので、その時の対処方法をあらかじめ確認できていなければ意味がありませんので、ここで自分で操作の練習をしておきましょう。

バックアップの操作を知る

SQL Management Studio で図のようの操作します。

バックアップは「データベース」を一つの単位として処理するので、バックアップしたい「データベース」を選択して操作します。

出てきたバックアップのウィンドウでオプション設定が可能です。基本的にはデフォルトの設定のままで問題ありませんが、知っておきたいポイントを解説します。

バックアップ先の設定

バックアップのファイルの保存先を指定します。せっかくバックアップしてもどこに保管されたのか分からないと困りますので、確認して必要であれば変更しておきましょう。

バックアップは *.bak という一つのファイルに出力されますので、これを指定しておきます。

バックアップの種類

バックアップの種類として次の3つのものがありますので、ここで理解しておきましょう。

完全
差分
トランザクションログ

「完全」バックアップ

データベース全体を丸ごと保存する方法です。

最も分かりやすい
復元が簡単
サイズは大きくなりやすい

SQL Server Express 利用者の基本はこれ で問題ありません。

「差分」バックアップ

前回のフルバックアップ以降に変更された部分だけを保存します。

フルよりサイズが小さい
復元時は「完全」バックアップと「差分」バックアップの両方のファイルが必要

データベースの規模が大きく、ディスク容量の消費が深刻な場合にはこの「差分」バックアップを活用する必要が出てきます。

Excel の拡張として SQL Server を利用する程度であれば、「完全」バックアップを活用する方が余計なトラブルを回避できます。

「トランザクションログ」（操作記録）バックアップ

トランザクション（transaction）とは「取引」の意味ですが、つまりデータベースに対するデータ操作のことです。

このデータ操作の一つ一つを記録して、その操作が実行される前の状態を巻き戻して復元することが可能です。

これは一般ユーザーが使うには手間な上にシステム停止のリスクもあるので推奨できませんが、プロはこういうバックアップも使っているということは知っておいても損はないと思います。

「完全」バックアップや「差分」バックアップがその時点のスナップショットのバックアップであるのに対して、「トランザクションログ」は、操作一つ一つに分解してデータを巻き戻すことができます。

そのため、「トランザクションログ」を使えば致命的なデータ破損事故があった場合にはその直前の状態まで復元できるということになります。

ちなみにデフォルトのデータベースの設定ではこの「トランザクションログ」のバックアップは選択することができません。この設定を変更するには、以下の操作を行います。

データベースのアイコンを右クリックして出たメニューから「プロパティ」をクリック
サブウィンドウの左に表示される「ページの選択」から「オプション」を選択
「復旧モデル」のプルダウン選択で、「完全」を選択

この設定をするとログの記録が始まり、「トランザクションログ」のバックアップをしない限りファイルが大きくなり続けます。

これを放置しておくと、コンピュータの記憶容量が一杯になってシステムが停止するという事態になりかねないので、注意が必要です。

このためバックアップの計画、自動化などを綿密に実行する必要がありますし、復元操作も間違いがないように行う必要があるなど、「トランザクションログ」を活用するのは、やはりプロ向けということになります。

バックアップの「追加」／「上書き」設定

デフォルトの設定ではバックアップを実行すると、バックアップファイルにその時のバックアップデータが追加されるようになっています。

つまり、毎日バックアップを取得している場合、10日バックアップを実行するとバックアップファイルには10日分のバックアップが保存されることになります。

これを毎日続けるとバックアップファイルの容量は非常に大きくなってしまうので、注意が必要です。

この動作を変更して、バックアップファイルは常に上書きして最新のバックアップのみを保管するには、バックアップのダイアログウィンドウで「メディアオプション」を選択し、図のように設定します。

復元の操作を知る

続いてバックアップファイルからデータベースを復元します。

一度別名のデータベースとして復元すると安全

復元操作で注意したいのは、デフォルトではデータベースはバックアップで上書きされてしまう ということです。

つまり、バックアップ作成時点から更新・追加したデータは全て失われるということになります。

これを回避したい場合は、別名のデータベースとして復元 することです。

これをすることで、データの中身を良く確認してから必要なデータだけをバックアップから取り出すこともできます。

もちろん、システムダウンなどでデータが全て失われた場合はバックアップでデータ全体を復元することで問題ありません。

復元操作の手順

SQL Server Management Studio でバックアップファイルからデータベースを復元するには図のように操作します。

バックアップファイルを選択して開く操作で簡単に復元ができます。

バックアップの「仕組み」の構築

SQL Server Management Studio を使ってマニュアル操作でバックアップと復元をしてみると案外簡単にできることが分かると思います。

また、バックアップへの理解も深まったことと思います。

理解が深まったところで、次は、いよいよバックアップの「仕組み」を構築していきます。

毎回マニュアルで操作しなくても自動でバックアップができるようにしていきます。

バックアップのSQLコマンドを理解する

バックアップの自動化のためにはバックアップのSQLコマンドを理解する必要があります。

まずはサンプルをご覧下さい。

BACKUP DATABASE SampleDB
TO DISK = 'D:\Backup\SampleDB.bak'
WITH INIT;

各行の意味は以下の通りです。特に難しいものはないと思います。

１行目： BACKUP DATABE がデータベースをバックアップするコマンドです。続く SampleDB はバックアップするデータベース名です。ここはご自分で管理するデータベース名に変更して下さい。
２行目： TO DISK でバックアップを保管するファイル名を指定します。
３行目： WITH INIT で 「完全」バックアップ に設定しています。

「差分」バックアップ を実行するには３行目を次のように直します。

WITH DIFFERENTIAL; -- 差分バックアップ

バックアップの計画

小さな規模のデータベースを運用するのに複雑なバックアップは不要です。分かりやすく、間違いが無いバックアップを計画しましょう。

そこで、ここでは次のようなバックアップをすることにします。

バックアップ計画（例）

人が操作しなくても自動でバックアップを実行
毎日昼休みに バックアップを実行
バックアップファイル名に日付 を入れてどの日のバックアップか一目で分かるようにする
7日以上前 のバックアップは削除

バックアップ自動実行のフロー

SQL Server Express を活用している場合は図のような流れでバックアップを自動実行します。

このフローを実現するために一つずつ作業を進めます。

*.sql ファイルの作成

バックアップコマンドのテキストを *.sql ファイルに保管します。

メモ帳に次の内容を記入して D:\Backup_MyDB.sql というファイル名で保存します。

なお、データベースの名前の SampleDB やバックアップファイルの名前の D:\Backup\SampleDB_ の部分はご自分の環境に合わせて適宜変更して下さい。

DECLARE @FileName NVARCHAR(260);

SET @FileName =
    'D:\Backup\SampleDB_'
    + CONVERT(VARCHAR(8), GETDATE(), 112)
    + '.bak';

BACKUP DATABASE SampleDB
TO DISK = @FileName
WITH INIT;

BACKUP DATABE コマンドの前の部分にやや複雑なコマンドが並んでいますが、これはバックアップファイル名の後半に年月日を８桁の数字で追加するものです。例えば次のようなファイル名が作成されます。

D:\Backup\SampleDB_20260401.bak

*.bat ファイルの作成

さきほど作成した Backup_MyDB.sql を実行するバッチファイルを作成します。

メモ帳で次の内容を記入して D:\Backup_MyDB.bat というファイル名で保存します。

sqlcmd -S localhost\sqlexpress -E -i "D:\Backup\Backup_MyDB.sql"

forfiles /p "D:\Backup" /m *.bak /d -7 /c "cmd /c del @path"

robocopy D:\Backup Z:\Backup

中身を解説します。

１行目 はSQLを実行する部分です。

SQL Server をインストールすると以下のようなパスに SQLCMD.EXE というコマンドがインストールされています。