開発時にとりあえずSQLServerを定期的にバックアップする

はじめに

開発時の傍らにSQLServerの定期バックアップをとりあえず、実施したい ＋　基本のキ です。
いろんな方が、説明してくれていますので、初心者向けに流れと補足をまとめてみました。
ググってなんだか難しそうと思った場合に参考になれば。
基本GUIで。

.mdf拡張子ファイルと.ldf拡張子ファイル

バックアップを理解する前提としてSQLServerのテーブル、レコードなどがどのように物理的に理解するとバックアップの理解がしやすいです。

• .mdf (データファイル)
  実際にデータが格納されているファイル。
  1ファイルだけなく、複数ファイルにも分割して格納することも可能
  複数ファイルに分割する場合、拡張子は、.ndfとなる。

• .ldf(トランザクションログファイル)
  単にログファイルともいいます。
  データベースの変更に対してのログ(履歴)を格納しているファイル。
  変更とは、insert、update等のSQLをイメージするとわかりやすいかと思います。
  1ファイルだけなく、複数ファイルにも分割して格納することも可能。
  複数ファイルに分割する場合の拡張子も、.ldfとなる。
  複数のファイルで構成されている場合でも、1ファイルずつ利用します。同時に2つ以上使うことはありません。

物理ファイルの格納先、ファイル確認方法

バックアップ対象の”データベース”が実際の上記のどの物理ファイルで管理されているかは、SQL Server Management Studio (SSMS)から対象のDBのプロパティからファイルで確認することができます。

.ldf(トランザクションログファイル)の肥大化

少し話はそれますが、トランザクションログファイルは、insert、update等のログ(履歴)を格納しているため、どんどんたまっていきます。
運用時はプロに任せるとして、開発・テスト時などもたまっていきます。
パフォーマンステスト、データ移行テストなどで、大量件データのロード、削除を繰り返すととんでもないサイズになりディスクサイズが足りなくなります。
※ディスクサイズが足りなくなった場合、ディスクサイズの開放するにも容量不足でエラーになるので、定期的に切り捨てが必要です。

.ldf(トランザクションログファイル)をバックアップしたときに不要になったトランザクションログを切り捨てるので、ディスクサイズの観点からも定期的にバックアップをする必要があります。
※トランザクションログが肥大化した場合、切り捨てをしてもディスクサイズは開放されず、中身がクリアされるだけなので、肥大化する前に定期的にバックアップ=切り捨てを行っておくと肥大化を防ぐことができます。

バックアップの種類

本題のバックアップです。
バックアップは以下の3種類があります。

完全バックアップ(フルバックアップ)　(full backup)

その名の通り完全なデータベースのバックアップで、データベースの全てのデータ、トランザクションログが含まれています。

• メリット
  • バックアップファイルのサイズが大きくなる
• デメリット
  • バックアップをとるに時間がかかる
  • バックアップファイルのサイズが大きくなる

差分バックアップ　 (differential backup)

一番最近の完全バックアップからの変更のみバックアップを取得します。
フルバックアップと同様、差分バックアップは、トランザクションログの一部もコピーします。
差分バックアップを何度とっても、一番最近の完全バックアップからの変更が含まれます。前回の取得した差分バックアップからのさらに差分ではない点を注意してください。
バックアップを適用する場合(リストア)は、最新の差分バックアップのみ適用することになります。

• メリット
  • 完全バックアップよりバックアップ取得の時間がかからない
  • 完全バックアップよりファイルサイズが小さくなる
• デメリット
  • 完全バックアップより時間がかからないが、完全バックアップを取得しない限り、バックアップ取得の時間、サイズともに増える。
  • バックアップを適用する場合(リストア)は、完全バックアップを適用後、差分バックアップを適用するため完全バックアップより手間

トランザクションログバックアップ　(transaction log backup)

.ldf(トランザクションログファイル)のバックアップです。
トランザクションログバックアップはフルバックアップ、差分バックアップと違い、データのバックアップとは関連がありません。公式ドキュメント
フルバックアップ、差分バックアップ取得していても前回のトランザクションログバックアップを取得してからの差分のトランザクションログをバックアップします。

３つをうまく組み合わせる

上記の3種類のバックアップは文字だけだとイメージがつきにくと思うので、以下のサイトで図解で分かりやすく説明されています。

実際の運用では、上記３つをうまく組み合わせて活用するのですが、以下のサイトで３つをどのタイミングで取得すればいいのか例をあげてくれています。

っで開発時はどうすればいいのか

上記は運用面を考慮した場合の仕組みですが、開発時は３つのバックアップを組み合わせはやりすぎかもしれません。
データ量にもよりますが、なにかあったときのためにとりあえずバックアップをお手軽にやるにはフルバックを1週間に1回にとる＋任意のタイミングで手動でとるでいいかもしれません。

手動バックアップ

任意のタイミングと取りたい場合

自動バックアップ

自動のタイミングと取りたい場合

自動バックアップ

上記はおてがるですが、世代管理をしていないのでバックアップ取得ごとに以前のバックアップは上書きでなくなってしまいます。
[メンテナンスプランウィザード] を使うことで、日付ごとなどでバックアップを取得したり、定期的に日数が経過したバックアップを削除することもできます。
また、上記だとトランザクションログのバックアップ=肥大化対策は別途設定を行う必要があるので、メンテナンスプランで設定したほうがすっきりすると思います。少々上記に比べて手順が多いですがなれれば難しいものではありません。
下記のサイトが参考になります。

参考サイト