SQLserverの バックアップは平和なときにやりましょう!(完全バックアップ) – ざったなぶろぐ
Twitterランキング※MarriaDBのレコードは800万Over!!
無料ホームページサービスのEHP

ストーリーズ機能が加わりました。

ストーリーズを開く

SQLserverの バックアップは平和なときにやりましょう!(完全バックアップ)

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

SQLserver バックアップ(完全バックアップ)

完全バックアップ

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

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

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

 

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

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

1回目

SQLサーバーのデータファイルが肥大化していく画像

2回目

SQLサーバーのデータファイルが肥大化していく画像

3回目

SQLサーバーのデータファイルが肥大化していく画像

4回目

SQLサーバーのデータファイルが肥大化していく画像

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

init [noinit]

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

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

 

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

SQLサーバーのデータファイルが肥大化していく画像

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

[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

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

SQLサーバーのデータファイルが肥大化しなかった画像

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

 STATS = 10

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

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

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

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

SQLサーバーのバックアップを行っている図

format [noformat]

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

SQLサーバーのバックアップセットのイメージ図

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

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

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

sqlservertでヘッダーだけリストアを行い、バックアップセットを除いている図

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

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

 

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

sqlservertでヘッダーだけリストアを行い、バックアップセットを除いている図

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

この様な動きをしますので、 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

 

sqlservertでバックアップを行っている図

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

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

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

上記を行えば、

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

と言う事になります。

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

 

 

 

タイトルとURLをコピーしました