[T-SQL] バックアップとリストアを行う

handcraft 5/6/2009 42245 4.88 SQL Server

T-SQL を使用して、バックアップとリストアを実行するサンプルを掲載します。バックアップファイルはSSIS (SQL Server Integration Service) を使用してパッケージを作成する方法があります。その場合は Visual Studio の SSIS プロジェクトから作成します。

動作確認は SQL Server 2005 Developer Edition で行い、T-SQL は SQL Server Management Studio 上で実施しています。 sql ファイルを作成してsqlcmd コマンドを使用しても動作すると思います。

1. バックアップ

1.1 データベースのバックアップ

ファイルにバックアップを作成する方法を掲載します。 BACKUP DATABASE ステートメントを使用してバックアップを作成します。オプションを指定しない限り、既存のバックアップファイルが存在する場合は、バックアップセットがファイルに追記されます。バックアップセットの有効期間を日付や期間で設定することもできます。

-- 完全バックアップを取得します
BACKUP DATABASE Component 
TO DISK = 'C:\work\Backup20090506.bak'

-- バックアップファイルがすでに存在する場合は
-- バックアップセットが追記されます
BACKUP DATABASE Component
TO DISK = 'C:\work\Backup20090507.bak'
WITH MEDIANAME = 'バックアップメディア名'
    ,MEDIADESCRIPTION = 'バックアップメディアの説明'
    ,NAME = 'バックアップセット名'
    ,DESCRIPTION = 'バックアップセットの説明'

-- 有効期限を2009/5/14までとしてでバックアップセットを作成する。
BACKUP DATABASE Component
TO DISK = N'C:\work\Backup20090508.bak'
WITH MEDIANAME = N'バックアップメディア名'
    ,MEDIADESCRIPTION = N'バックアップメディアの説明'
    ,NAME = N'バックアップセット名'
    ,DESCRIPTION = 'バックアップセットの説明'
    ,EXPIREDATE = N'05/14/2009 00:00:00'

-- 有効期間を30日としてバックアップセット作成
BACKUP DATABASE Component
TO DISK = N'C:\work\Backup20090508.bak'
WITH MEDIANAME = N'バックアップメディア名'
    ,MEDIADESCRIPTION = N'バックアップメディアの説明'
    ,NAME = N'バックアップセット名'
    ,DESCRIPTION = 'バックアップセットの説明'
    ,RETAINDAYS = 30

複数ファイルに分割してバックアップセットを作成することもできます。また、MIRROR TO を使用することで、ミラー化されたバックアップセットを作成できます。ミラー化することで、冗長性を持たせることができます。ミラー化した場合、分割したバックアップセットと異なり、一つのバックアップセットで復元できます。分割バックアップとミラー化は同時にしてできます。同時に使用することでメディアの冗長化とバックアップパフォーマンスの向上を行うことができます。

-- 複数ファイルに分割してバックアップセットを作成する
BACKUP DATABASE Component
TO DISK = N'C:\work\Backup20090508_1.bak'
  ,DISK = N'C:\work\Backup20090508_2.bak'
  ,DISK = N'C:\work\Backup20090508_3.bak'

-- ミラー化されたメディア セットの作成
BACKUP DATABASE Component
       TO DISK = N'C:\work\CompBackup1.bak'
MIRROR TO DISK = N'C:\work\CompBackupMirror1.bak'
MIRROR TO DISK = N'C:\work\CompBackupMirror2.bak'
WITH FORMAT

通常のバックアップの順序に影響を与えないように、その時点のバックアップを作成するには COPY_ONLY オプションを指定してバックアップします。

BACKUP DATABASE Component
       TO DISK = N'C:\work\CompBackup.bak'
WITH COPY_ONLY

完全バックアップをとった後は、データベースの差分バックアップをとることができます。差分バックアップはDIFFERENTIAL オプションを指定します。

BACKUP DATABASE Component
       TO DISK = N'C:\work\CompBackup.bak'
WITH DIFFERENTIAL

バックアップデバイスをSQL Server に登録することで、バックアップ時にファイルやテープを指定するのではなく、バックアップデバイスを指定できるようになります。バックアップの登録には sp_addumpdevice を使用します。登録されたバックアップデバイスは sys.backup_devices カタログビューから参照できます。バックアップデバイス情報の表示には、 sp_helpdevice, 削除には sp_dropdevice を使用します。

-- バックアップデバイスをSQL Server 2005 に登録する
sp_addumpdevice @devtype = 'disk'
               ,@logicalname = N'ComponentBackup'
               ,@physicalname = N'C:\work\CompBack.bak'

-- 追加されたバックアップデバイスを確認
select *
  from sys.backup_devices

-- バックアップデバイス情報を検索
EXEC sp_helpdevice 'ComponentBackup'

-- バックアップデバイスに対してバックアップを行う
BACKUP DATABASE Component
    TO ComponentBackup

-- バックアップデバイスを削除する
sp_dropdevice 'ComponentBackup'

バックアップデバイスはManagement Studio 上からも、バックアップ、リストア時に指定できます。バックアップデバイス指定画面でバックアップメディアにバックアップデバイスを選択することで指定します。

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

BACKUP LOG ステートメントを使用してトランザクションログのバックアップを作成します。指定可能なオプションは BACKUP DATABASE とほぼ同じですが、 NO_TRUNCATE, NORECOVERY, STANDBY などのオプションが使用できます。詳細は Books Online を参照してください。

また、サンプルでは、 DBCC SHRINKDATABASE を使用して、ログファイルを圧縮するサンプルも掲載しています。

-- トランザクションログのバックアップ
-- 使用できるオプションはBACKUP DATABASE と同じだが
-- NO_TRUNCATE, NORECOVERY, STANDBYなどがあります。
BACKUP LOG  Component
    TO DISK = N'C:\work\CompBackup.trn'

-- データベース、トランザクションログ
-- のバックアップが完了したら、トランザクション
-- ログを圧縮する
DBCC SHRINKDATABASE(N'Component')

1.3 バックアップ情報の取得

バックアップメディアに対して、 RESTORE LABELONLY , RESTORE FILELISTONLY, RESTORE HEADERONLY, RESTORE VERIFYONLY ステートメントを使用するとバックアップセットの情報を取得できます。

-- バックアップ メディアについての情報が含まれている結果セットを返します。
-- バックアップ メディアは、指定したバックアップ デバイスで識別されるメディアです。 
RESTORE LABELONLY 
  FROM DISK = N'C:\work\Backup1.bak'

-- バックアップ セットに保存されているデータベースとログ ファイルのリスト
-- を含んだ結果セットを返します。
RESTORE FILELISTONLY
  FROM DISK = N'C:\work\Backup1.bak'

-- 特定のバックアップ デバイス上にあるすべてのバックアップ セットについて、
-- すべてのバックアップ ヘッダー情報を含む結果セットを返します。 
RESTORE HEADERONLY
  FROM DISK = N'C:\work\Backup1.bak'

-- 復元せずにバックアップを確認して、バックアップ セットが完全なものであること、
-- およびバックアップのすべてが読み取り可能であることを確認します
RESTORE VERIFYONLY
  FROM DISK = N'C:\work\Backup1.bak'

2. リストア

バックアップセットからデータベースを復元するサンプルを掲載します。リストアを行うにはRESTORE DATABASE ステートメントを使用します。復元後のデータベースで使用する、データベースファイルを移動する場合は、MOVE TO オプションを使用します。

-- データベースをリストアする
-- バックアップメディアの1番目のバックアップセット
-- を既存のデータベースを上書きするモードで復元
USE master;

RESTORE DATABASE Component
   FROM DISK = N'C:\work\BackupTest.bak'
   WITH FILE = 1, REPLACE

複数ファイルに分割されたバックアップファイルをリストする場合は、FROM DISK で複数のバックアップファイルを指定します。

完全バックアップからリストアを行い、トランザクションログからリストアを行う場合は,完全バックアップをNORECOVERY オプションで復元し、トランザクションログを RECOVERY オプションを使用してリストアします。

USE master;

-- データベースのリストア
-- トランザクションのリストアを行うサンプル
BACKUP DATABASE Component
       TO DISK = N'C:\work\BackupTest.bak'
BACKUP LOG  Component
    TO DISK = N'C:\work\BackupTest.trn'


RESTORE DATABASE Component
   FROM DISK = N'C:\work\BackupTest.bak'
   WITH FILE = 1, REPLACE, NORECOVERY

-- トランザクションログのリストア
RESTORE LOG Component
FROM DISK = N'C:\work\BackupTest.trn'
WITH FILE = 1, RECOVERY, REPLACE

最後に、差分バックアップを含む場合のリストアサンプルです。完全バックアップ、差分バックアップ、トランザクションログの順にリストアを実施します。

-- 差分バックアップをリカバリする場合
BACKUP DATABASE Component
    TO DISK = N'C:\work\Backup1.bak'
BACKUP DATABASE Component
    TO DISK = N'C:\work\Backup1.bak'
WITH DIFFERENTIAL
BACKUP LOG Component
    TO DISK = N'C:\work\Backup1.bak'

RESTORE DATABASE Component
   FROM DISK = N'C:\work\Backup1.bak'
   WITH FILE = 1, REPLACE, NORECOVERY

RESTORE DATABASE Component
   FROM DISK = N'C:\work\Backup1.bak'
   WITH FILE = 2, NORECOVERY

RESTORE LOG Component
   FROM DISK = N'C:\work\Backup1.bak'
   WITH FILE = 3, RECOVERY

複数ファイルに分割されたバックアップファイルをリストする場合は、FROM DISK で複数のバックアップファイルを指定します。

3.まとめ

サンプルには記載していませんが、リストアはファイルグループ単位や、部分(PARTIAL) バックアップなども行うことができます。詳細はBooks Online を参照してください。

説明は以上です。誤り、指摘点などがありましたらご連絡ください。

// Net planet-es V2