Backup　

Summary

バックアップでは以下のことが行えます。

ユーザーが接続中にバックアップ可能。
スキーマ、ファイル構造、データ、トランザクションログの一部をバックアップ。
データベースのチェックポイントを事項氏、最も古いアクティブなトランザクションログレコードのログシーケンス番号（LSN）を記録する。
すべてのページをバックアップ。
トランザクションログレコードの保存。

実行できるユーザーはデフォルトでは、下記のロールのユーザーです。

sysadmin
db_owner
db_backupoperator

System Database Backup

システムデータベースのバックアップのタイミングは次のような変更を行ったときに行います。

master

データベースの作成、および変更後。
ユーザーアカウントの作成、および変更後。
環境設定変更後。
システムストアドプロシージャ実行後。
サーバーの追加、および削除後。
ユーザー定義メッセージの作成、および削除後。

msdb

ジョブの作成、および変更後。
オペレータの作成、および変更後。
警告の作成、および変更後。

Recovery Model

Recovery Model	特徴
フル	トランザクションログの自動削除を行わない。障害がおきても、処理中のトランザクション以外はすべて復元可能。
一括ログ記録	トランザクションログは削除しないが、一部の操作（CREATE INDEX、WRITETEXT、UPDATETEXT）に関しては、ログの書き込みを最小限、もしくは書き込みません。
シンプル	チェックポイントごとにトランザクションログを削除します。

Permanent Backup Files

SQL Server 6.5 以前ではバックアップデバイスと呼ばれていたものです。バックアップを保存するメディアを予め作成しておくことにより、作成時の論理デバイス名でバックアップを行うことが可能です。

作成の方法は、Enterprise Manager のバックアップフォルダのショートカットメニューから [新規バックアップデバイス] を選択します。もしくは、sp_addumpdevice ストアドプロシージャにて作成が可能です。

パーマネントバックアップファイルを作成すると、一つのバックアップを複数のメディアに対して、並列バックアップも可能になります。

以下は、master データベースをバックアップする例です。

ex)
USE master
EXEC sp_addumpdevice 'disk', 'mydisk', 'c:\backup\master.bak'

BACKUP DATABASE master TO mydisk

Backup

フルデータバックアップ

システム障害時のベースとなります。すべての情報をバックアップします。このバックアップだけで、完全に復旧しますが、バックアップには時間がかかります。

ex)
BACKUP DATABASE DatabaseName TO DISK = 'filepath'
BACKUP DATABASE DatabaseName TO PermanentFileName

差分バックアップ

前回のフルバックアップ以降に変更された情報のみバックアップを行います。頻繁に更新がかかるデータベースでは、フルバックアップと組み合わせることにより、バックアップの時間を短縮できます。

ex)
BACKUP DATABASE DatabaseName TO DISK = 'filepath'
WITH DIFFERENTIAL

トランザクションログバックアップ

フルバックアップ実行後に、データベースの変更記録をバックアップできます。シンプルモデルの場合には使用できません。バックアップ後は、アクティブでないトランザクションログを切り捨てます。

ex)
BACKUP LOG DatabaseName TO DISK = 'filepath'
　

BACKUP オプション

オプション	説明
INIT	上書きバックアップ
NOINIT	追加バックアップ
NO_TRANCATE	最新のデータベースアクティビティすべてをバックアップします。トランザクションログを削除しません。
TRANCATE_ONLY NO_LOG	バックアップを取らないでトランザクションログのみを削除します。

オフラインバックアップ

推奨されているかはわかりませんが、SQL サービスが停止できる場合 *.mdf と *.ldf のファイルをコピーしておけば問題ありません。復元するときも元の場所にコピーするだけで OK です。

Restore

RESTORE オプション

オプション	説明
RESTORE HEADERONLY	バックアップメディアのヘッダー情報のみを取得できます。
RECOVERY	もう、戻すべきバックアップファイルがないとき（最新の状態に戻すとき）に指定します。SQL Server を通常の状態に戻します。
NORECOVERY	まだ、戻すべきバックアップファイルがあるときに指定します。データベースは使用不可の状態です。
STANDBY	まだ、戻すべきバックアップファイルがあるときに指定します。ただし、データベースへの読み取りアクセスのみ許可されます。リストアのテストや、ログ配布の時に便利です。

フルバックアップからの復元

ex)
RESTORE DATABASE DatabaseName
FROM BackupDevice

差分バックアップからの復元

ex)
RESTORE DATABASE DatabaseName
FROM BackupDevice
WITH NORECOVERY

トランザクションログバックアップからの復元

ex)
RESTORE LOG DatabaseName
FROM BackupDevice

Backup/Restore Plan

以下のシナリオのときの復旧方法についてご紹介します。

フルバックアップ
データの INSERT
トランザクションログのバックアップ
データの INSERT
差分バックアップ
データの INSERT
トランザクションログのバックアップ
！障害！

この場合、以下の手順で戻します。

フルバックアップをリストア（WITH NORECOVERY）
差分バックアップをリストア（WITH NORECOVERY）
手順 7 で取得したトランザクションバックアップをリストア（WITH RECOVERY）

最初に取得したトランザクションログのバックアップは、差分バックアップ内に含まれているので、戻す必要はありません。また、リストア手順の 1 と 2 では、[WITH NORECOVERY] をつけないと、その後のバックアップファイルを戻すことができなくなります。[WITH NORECOVERY] は Enterprise Manager では、オプションタブで [データベースは操作不可状態。別のトランザクションログの復元は可能] にチェックをします。