SQLserverのバックアップは平和なときにやりましょう！（完全バックアップ）

この記事を読むのに必要な時間は約 8 分です。

SQLserver バックアップ（完全バックアップ）

SQLserver バックアップ（完全バックアップ）

完全バックアップ

SQL Serverのデータベースについてバックアップを行いたいと思います。バックアップは完全バックアップを前提として説明します。※差分バックアップではない

早速ですが、SQL文の最小構成は以下のような形となります。

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' 
GO

ただし、このままマネをしては絶対にいけません。この例はオプションを一切利用しておりません。そのため、この内容を反復して実行すると大変な事になります。

どのような大変な事になるかと言うと・・・・

１回目

２回目

３回目

４回目

もうこれだけやればわかると思いますが、サイズがどんどん肥大化しています。オプションを何も指定しない状態でバックアップを繰り返すと、既存のバックアップセットの後ろに追記を繰り返します。そのため、ファイルのサイズがどんどん肥大化していきますのでハードディスクの容量をどんどん消費してしまいます。

init [noinit]

ファイルの肥大化問題を防ぐためにオプションを指定します。SQL文は以下の通りです。

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' 
with noinit
GO

オプションで　with noinit　を付け加えています。実行結果は以下の通りです。

はい！ちゃんと肥大化してしまいました。

[speech_bubble type=”drop” subtype=”R1″ icon=”02ukiyoe/tae_L.png” name=”たえさん”]なにやってますの？[/speech_bubble]
と、いう事でオプションを指定しない場合はデフォルト値として noinitが設定されている状態と同じと言う事になります。

このままバックアップを繰り返すとディスクを消費するばっかりなので with init でバックアップを取得します。

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' 
with init
GO

実行結果は以下の通りです。

ちゃんとサイズが少なくなりました。

STATS = 10

バックアップを取得している最中に、現在どの程度作業が進んでいるのか、DBサイズが大きいと気になります。

バックアップの進行状況を表示するオプションが　STATS　です。　数字でどの程度の割合で表示するかを指定できます。試しに10（%）で実行してみたいと思います。

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' 
with 
　　　 init
　　　,stats=10
GO

以下の通り、バックアップ状況を表示してくれます。あまり細かい値をしていしても仕方が無いので、10(%)ぐらいで表示するのが良いです。

format [noformat]

バックアップはバックアップメディアの中でバックアップセットごとに管理されます。

バックアップメディアの中身は以下のコマンドで見る事ができます。

RESTORE HEADERONLY FROM DISK = N'G:\file\DBBK\test001.bak'

同じバックアップメディアに4回バックアップを行った結果は以下の通りです。

format オプションで規定ではnoformatが指定されている状態と同じなので、formatオプションを指定してバックアップを取得します。

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' 
with 
　　　 format
      ,name=N'テストバックアップ'
　　　,stats=10

formatオプションを指定してバックアップを行うと、バックアップメディアが初期化されます。

※取得済みのバックアップセットが削除されますので注意

この様な動きをしますので、 format を行いつつ　noinit を行うと怒られます。

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' 
with 
　　　 format
      ,noinit
      ,name=N'テストバックアップ'
　　　,stats=10

メッセージ 3031、レベル 16、状態 1、行 1
オプション ‘format’ と ‘noinit’ が競合しています。競合したオプションを削除してから、ステートメントを再実行してください。

SKIP, NOREWIND, NOUNLOAD等

skipオプションはメディアの有効期限の話、NOREWIND, NOUNLOADは現在はあまり使われないテープのオプションですので、説明は省きます。また、例ではExpressエディションの為利用できませんが、上位のエディションでCOMPRESSIONオプションでバックアップを圧縮する事もできます。※通常のWindows上で圧縮を行えば1/5程度のサイズまで小さくなります。

完全バックアップを行う場合、バックアップ取得時の時間に復元すれば良いのであれば、以下のオプションを指定バックアップを行えば十分です。

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' 
with 
　　　 format
　　　,stats=10

※毎日夜中の12時にバックアップを行い、何かトラブルがあった際に、前日の12時の状態にまで戻せればよい場合や、1hに一回バックアップを行い問題が発生時に、最大1時間前の状態に戻せれば良い場合など

ある程度バックアップのサイズが大きくなっても良い場合は夜中の12時に以下を１回実行

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' with format,stats=10

その後、1hに一度以下を実行

BACKUP DATABASE [test001] TO  DISK = N'G:\file\DBBK\test001.bak' with stats=10

上記を行えば、

最悪、前日の夜間12時まで復元可能
その後、任意の1h毎のタイミングまで復元可能

と言う事になります。

完全バックアップはログの管理が不要ですので運用が簡易になります。