2013年2月20日水曜日

◆オンラインバックアップ

 

完全バックアップ
バックアップ
  1. 「ＳＱＬＳｅｒｖｅｒ　Ｍａｎａｇｅｍｅｎｔ　Ｓｔｕｄｉｏ」を起動
  2. 対象のDBを右クリックし「タスク」「バックアップ」を選択
    image
  3. 表示された「データベースのバックアップ」ダイアログで「バックアップの種類」に「完全」を指定し「OK」ボタンをクリック
    image
    ちなみに、バックアップ先は複数定義しておけるが、どれかにバックアップを取るというのではなく、冗長構成のためのものと思われる。なので、ここでいくつ場所を指定しても論理的には１つのバックアップと扱われるようだ。

    ちょっと調べてみたが、バックアップ先に複数ファイルを指定できるのは冗長化のためでは無さそう。 冗長化には「MIRROR TO」句が別途用意されている。（GUIでは見当たらない）
    じゃあ何のため？となるのだが、これがまたさっぱり説明がない。一応メディア セット、メディア ファミリ、およびバックアップ セット (SQL Server)にトピックはあるのだが、いつもの通りもともと判っている人以外には理解ができない説明。
    あとは想像するしかないのだが、今となっては使わなくなった「テープ」向けの機能かな？と思ったりする。テープの場合は必ずしも1本で完結するわけではないだろうから複数メディアで論理的なメディアを構成する仕組みも必要になりそうだ。
    DISKの場合はFatの時代でもないので制限もないだろうから、ここでの複数指定は不要な機能と判断した。
  4. 「正常」表示されればバックアップは完了
    image
  5. テストのためにDBを展開し、テーブルのデータを削除してみる（ここではNorthwndの「Order Details」テーブルを全件削除）
    image
  6. Selectして０件になったのを確認する
    image
リストア
  1. 「ＳＱＬＳｅｒｖｅｒ　Ｍａｎａｇｅｍｅｎｔ　Ｓｔｕｄｉｏ」を起動
  2. 復元対象のDBを右クリックし、「タスク」「復元」「データベース」をクリック
    image
  3. 「データベースの復元」ダイアログが表示されたら「復元するバックアップセットの選択」から完全バックアップが選択されているのを確認して「オプション」ページをクリックする。
    image
  4. 「オプション」ページの「復元オプション」で「既存のデータベースを上書きする」をチェック、「ログ末尾のバックアップ」で「復元の前にログ末尾のバックアップを実行する」のチェックを外して「OK」
    image
  5. ここで、以下のように「クエリエディタ」が開いているとエラーになるので全て閉じておく
    image
    image
  6. 以下のダイアログが表示されれば「OK」
    image
  7. 「Order Details」テーブルを開きデータがリストアされているのを確認する
    image
BACKUP／RESTOREステートメントによるバックアップと復元

GUI操作ではなくSQLステートメントを使用してオンラインバクアップを実行する場合には「BACKUP DATABASE」ステートメントを利用する。

image

リストアするときは「RESTORE DATABASE」ステートメントを利用する。

image

バックアップ

Northwndデータベースを「D:\data\SQLServer2012\OnlineCmdBkup\Norwhwnd.bak」にバックアップしてみる

backup database NORTHWND
  to disk = 'D:\data\SQLServer2012\OnlineCmdBkup\Norwhwnd.bak'

image

ちょっと話がそれるが、このときバックアップファイルに対して「MSSQLSERVER」サービスからのファイルアクセス権が必要になる。

ということで「MSSQLSERVER」がどんなサービスアカウントで実行されているか確認してみると以下のようなちょっと見慣れないアカウントで実行されていた。
image

これは「NT Service\サービス名」というアカウントで2008R2あたりから導入されている「仮想アカウント」と言うものらしい。
基本的にはこれまでの「Netowork Service」と同様のアカウントだが、どのサービスで使われているのか素性を明らかにするために名前にサービス名を含むようにしたらしい。
また、ドメインアカウントが必要な場合は「管理されたサービスアカウント」なるものが使えるらしい。
こちらは、パスワードを自動管理してくれる対話ログオンを許さないサービス専用のアカウントみたいな感じ。
サービスアカウントの新機能 « MCTの憂鬱

もとに戻って、バックアップが取れたので先ほどと同様に「Order Details」テーブルを全件削除してリストアをテストする準備をしておく。

リストア

先ほどのバックアップからコマンドでリストアする

USE master
RESTORE DATABASE Northwnd
FROM DISK = 'D:\data\SQLServer2012\OnlineCmdBkup\Norwhwnd.bak' WITH REPLACE

image

「Order Details」テーブルを開きデータが戻っているのを確認する。

なお、リストアするときに排他アクセスできない場合は以下のコマンドでシングルユーザーモードにして他の接続を強制的に切断する。

-- データベース接続を強制終了する場合
USE master
ALTER DATABASE Northwnd SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- 確認
SELECT user_access_desc　FROM sys.databases WHERE name = 'northwnd'

-- 戻す
ALTER DATABASE Northwnd SET MULTI_USER

スクリプト（BACKUP／RESTORE）の生成機能
  1. 「SQLServer Management Studio」を起動
  2. 「オブジェクトエクスプローラ」で対象のデータベースを右クリックして「タスク」「バックアップ」を選択
  3. 「データベースのバックアップ」画面で「スクリプト」メニューをドロップダウン
    image
  4. いずれかの保存メニューをクリックするとバックアップのコマンドが生成される。（ここでは新規クエリウィンドウに保存）
    image
スケジュール設定によるバックアップの定期実行
定期実行
  1. 「Win + R」で「ファイル名を指定して実行」を開き「services.msc」で「サービス」を開く
  2. 「SQL Server エージェント」サービスが実行されているか確認する。（定常的に使うのであれば「スタートアップの種類」を「自動」にしておく
  3. 「SSMS」で「SQL Server エージェント」を展開し、「ジョブ」フォルダの右クリックから「新しいジョブ」をクリック
    image
  4. 「新しいジョブ」ウインドウで「名前」に任意の名称を指定して「ステップ」をクリック
    image
  5. 「ステップ」ページで「新規作成」ボタンをクリック
    image
  6. 「新しいジョブステップ」ページで「ステップ名」に任意のステップ名、「種類」に「Transact-SQLスクリプト」、「コマンド」に以下のテスト用バックアップコマンドを指定して「OK」

    BACKUP DATABASE Northwnd
      TO DISK = 'D:\DBBackup\test\Northwnd.bak'


    image
  7. 「新しいジョブ」に戻ったら「スケジュール」を選択したのち「新規作成」ボタンをクリック
  8. 表示された「新しいジョブ　スケジュール」で実行したいスケジュールを設定して「OK」
  9. 「新しいジョブ」へ戻って「OK」
  10. 指定したスケジュールでバックアップが実行されるのを確認する
ジョブの手動実行

スケジュール下ジョブは手動で実行することもできる。

  1. 「SSMS」「オブジェクトエクスプローラ」で「SQL Server エージェント」「ジョブ」から登録したスケジュールジョブを右クリックして「ステップでジョブを開始」をクリック
    image
  2. 本来は「XXXXX…」なのでいきなり実行されないはずだが、お約束違でジョブがいきなり実行される。以下の「成功」が表示されればOK。
    image
  3. 実行結果を確認するには同様にジョブを右クリックして「履歴の表示」を選択
    image
  4. 実行結果やメッセージなどが確認できる
    image
バックアップセットとは

SQLServerのバックアップでは、１つのバックアップファイルに複数世代が追加されてバックアップされていく。
これを「バックアップセット」と呼んでいて以下のコマンドでセットを確認できる。

RESTORE HEADERONLY FROM DISK = 'バックアップファイルパス'

image

また、それぞれのバックアップセットを区別しやすくするために「名前」や「説明」を付加しておくことができる。
image

コマンドではこんな感じ。

BACKUP DATABASE Northwind
TO DISK = ‘D:\DBBackup\test\Northwind.bak'
WITH NAME = 'NorthWind-完全 データベース バックアップ', DESCRIPTION = '説明のテスト'

あらら、コマンドでバックアップしたほうは文字化け。
image

「N」指定が必要みたい。

BACKUP DATABASE Northwind
TO DISK = ‘D:\DBBackup\test\Northwind.bak'
WITH NAME = N'NorthWind-完全 データベース バックアップ', DESCRIPTION = N'説明のテスト'

複数バックアップ セットがある場合のリストア（FILE オプション）

同じファイル内に複数のバックアップ セットがある場合、そのままリストアすると一番古いバックアップが適用される。

リストアするバックアップの世代を指定するには「File」オプションでバックアップのセット（番号）を指定する。
以下の「Position」を指定すれば良さそう。

image

USE master
ALTER DATABASE Northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE Northwind
FROM DISK = 'D:\DBBackup\test\Northwind.bak' WITH FILE = 1
ALTER DATABASE Northwind SET MULTI_USER

FORMAT オプションでバックアップ ファイルのフォーマット

「BACKUP」コマンドに「FORMAT」オプションを付けて実行するとバックアップセットの中に複数世代管理するのではなく、常に最新のものが１つだけ含まれるようになる。

自習書的にはこれがお勧めとのこと。
確かに、MSDNドキュメントでは様々なオプションの組み合わせによる様々な手法が説明されているが、我々末端ユーザーからするとバックアップは安全確実なのが一番なので自習書に従いたい。

use NorthWind
BACKUP DATABASE Northwind
TO DISK = 'D:\DBBackup\test\Northwind.bak'
WITH NAME = N'NorthWind-完全バックアップ', DESCRIPTION = N'Formatオプション付き' , FORMAT

ちなみに、これをGUIからやるには「データベースのバックアップ」で「オプション」を選択し、「新しいメディアセットにバックアップし、すべての既存のバックアップセットを消去する」をチェックすればよい。
これはその下の「新しいメディアセット名」の指定が必要に感じられるが、指定しなければ既存のファイルがそのまま使われるようだ。

image

バックアップのファイル名に日付け／時刻を入れる方法

以下のSQLで「日付／時刻」をファイル名にしたバックアップファイルの作成が可能になる。確かに、バックアップセットに複数突っ込んでいくよりはこっちの方が間違いがなさそう。
--
っと思ったが、どうなんでしょうね。
一応この自習書は2012用なのだが、実際試していると過去バージョンの説明を単に持ってきている部分が多くちゃんと検証しているのかしらん？とも思える。
2012ではリストア時のデフォルトは最新バージョンのバックアップから戻すのがデフォルトに変わっているし、GUIでバックアップのタイムラインが確認できたりもするので、自前で古くなったバックアップを整理する作業などを考えると単一ファイルに突っ込んですべてsqlserverにお任せするのもありかなと思う。
--

USE NorthWind
DECLARE @d datetime = GETDATE()
DECLARE @d1 char(8) = CONVERT(char(8), @d, 112)
DECLARE @d2 char(6) = REPLACE( CONVERT(char(8), @d, 108), ':', '' )
DECLARE @fName varchar(100) = 'D:\DBBackup\test\' + @d1 + @d2 + '.bak'
BACKUP DATABASE NorthWind
TO DISK = @fName

ちなみに、こういう日付を扱うような場合は「PowerShell」の方が簡単

$now = (get-date).ToString("yyyyMMddHHmmss")
Backup-SqlDatabase -Database Northwind -BackupFile "D:\DBBackup\test\$now.bak" -ServerInstance localhost

バックアップ圧縮

SQLServer2008から提供されているバックアップ圧縮機能。
バックアップおよびリストア時の読み込み・書き込み量を減らせるのでパフォーマンスが向上するらしい。当然バックアップディスクの容量も減らせて良いこと尽くし。
デメリットは若干のCPU使用率のUPだがそれほどでもなさそう。

圧縮してバックアップするには「オプション」で「圧縮」を指定すれば良い。
image

実際にバックアップされたファイルサイズを見てみると約５倍程度の違いが出ている。
image

コマンドでバックアップするには「COMPRESSION」オプションを指定すれば良い。

BACKUP DATABASE Northwind
TO DISK = 'D:\data\BackupTest\NorthwindCommand.bak' WITH COMPRESSION

ちなみに、個別に圧縮指定をしなくてもデフォルトで圧縮バックアップにすることもできる。
image

なお、リストアについてはバックアップファイルの圧縮・非圧縮を意識する必要は無い。

トランザクションログの管理
ログの肥大化

デフォルトではハードディスクの空き容量がなくなるまでログは増え続ける。
ログが満杯になると、エラー「9002」が発生する。
image

ログの肥大化を抑止するには
  1. トランザクションログを定期的にバックアップする
  2. 普及モデルを「単純」にする

トランザクションログのバックアップを行うと使用済み領域を切り捨てることができる。
image

使用済み領域を切り捨てることによって、その部分が再利用可能になるので、それ以上の肥大化は防げる。
ただし、ファイル自体を縮小するわけではないので、一旦拡張されたファイルサイズはそのままになる。

ファイルサイズ自体を縮小したい場合は以下のコマンドで。

DBCC SHRINKFILE(論理名,縮小後のサイズ）

ここで、論理名とは
image

縮小後のサイズは（MB）単位。
省略すると「初期サイズ」に戻るのかな。

ログの使用量を確認するには以下のコマンドで。

DBCC SQLPERF(LOGSPACE)

image

投稿者 時刻:
ラベル:

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)