データベースの完全バックアップを作成する (SQL Server)

2017-06-13

このトピックでは、SQL Server Management Studio、Transact-SQL、または PowerShell を使用して、SQL Server 2014 でデータベースの完全バックアップを作成する方法について説明します。

注

Azure Blob Storage サービスへの SQL Server バックアップの詳細については、「Azure Blob Storage サービスを使用した SQL Server のバックアップと復元」を参照してください。

このトピックについて

作業を開始する準備:

制限事項と制約事項

推奨事項

セキュリティ
データベースの完全バックアップを作成するには、次を使用します。

SQL Server Management Studio

Transact-SQL

PowerShell
関連タスク

開始する前に

制限事項と制約条件

BACKUP ステートメントは、明示的または暗黙的なトランザクションでは使用できません。
SQL Server によって作成されたバックアップは、それより前のバージョンの SQL Serverでは復元できません。
詳細については、「バックアップの概要 (SQL Server)」を参照してください。

推奨事項

データベースのサイズが大きくなると、データベースの完全バックアップの完了に時間がかかり、より多くのストレージ領域が必要になります。そのため、大規模なデータベースの場合は、データベースの完全バックアップを一連の 差分データベースバックアップで補完することが必要になる場合があります。詳細については、「差分バックアップ (SQL Server)」を参照してください。
sp_spaceused システムストアドプロシージャを使用して、データベースの完全バックアップのサイズを見積もることができます。
既定では、バックアップ操作が成功するたびに、 SQL Server エラーログおよびシステムイベントログにエントリが 1 つ追加されます。ログを非常に頻繁にバックアップする場合、これらの成功メッセージはすぐに蓄積され、その結果、他のメッセージの検索が困難になる可能性のある巨大なエラーログが生成されます。このような場合は、トレースフラグ 3226 を使用してこれらのログエントリを抑制できます(スクリプトがそれらのエントリに依存していない場合)。詳細については、トレースフラグ (Transact-SQL)に関する記事を参照してください。

安全

データベースバックアップでは TRUSTWORTHY が OFF に設定されています。 TRUSTWORTHY を ON に設定する方法については、「 ALTER DATABASE SET Options (Transact-SQL)」を参照してください。

SQL Server 2012 以降では、バックアップを作成するための PASSWORD と MEDIAPASSWORD のオプションは廃止されました。パスワードを使用して作成されたバックアップを復元することもできます。

権限

BACKUP DATABASE 権限と BACKUP LOG 権限は、既定では、 sysadmin 固定サーバーロール、 db_owner 固定データベースロール、および db_backupoperator 固定データベースロールのメンバーに与えられています。

バックアップデバイスの物理ファイルに対する所有と許可の問題によって、バックアップ操作が妨げられることがあります。 SQL Server では、デバイスに対して読み書きを実行できる必要があります。 SQL Server サービスが実行されているアカウントには書き込み権限が必要です。ただし、システムテーブルにバックアップデバイスのエントリを追加する sp_addumpdeviceでは、ファイルアクセスの権限は確認されません。バックアップデバイスの物理ファイルに関するこのような問題は、バックアップまたは復元が試行されたときに物理リソースにアクセスするまで表示されない場合があります。

SQL Server Management Studio の使用

注

SQL Server Management Studio を使用してバックアップタスクを指定する場合は、[スクリプト] ボタンをクリックしてスクリプトの変換先を選択することで、対応する Transact-SQL BACKUP スクリプトを生成できます。

データベースをバックアップするには

Microsoft SQL Server データベースエンジンの適切なインスタンスに接続したら、オブジェクトエクスプローラーでサーバー名をクリックしてサーバーツリーを展開します。
[データベース]を展開し、目的のデータベースに応じて、任意のユーザーデータベースを選択するか、または [システムデータベース] を展開して任意のシステムデータベースを選択します。
データベースを右クリックし、[ タスク] をポイントして、[ バックアップ] をクリックします。 [データベースのバックアップ] ダイアログボックスが表示されます。
Databaseリストボックスで、データベース名を確認します。必要に応じて、このボックスの一覧から別のデータベースを選択することもできます。
任意の復旧モデル (FULL、BULK_LOGGED、SIMPLE) のデータベースバックアップを実行できます。
[バックアップの種類] ボックスの一覧の [完全]をクリックします。

データベースの完全バックアップを作成した後、データベースの差分バックアップを作成できることに注意してください。詳細については、「データベースの差分バックアップの作成 (SQL Server)」を参照してください。
必要に応じて、 [コピーのみのバックアップ] を選択して、コピーのみのバックアップを作成します。 コピーのみのバックアップは、従来の SQL Server バックアップのシーケンスに依存しない SQL Server バックアップです。詳細については、「 Copy-Only バックアップ (SQL Server)」を参照してください。

注

[差分] オプションを選択した場合、コピーのみのバックアップを作成することはできません。
[バックアップコンポーネント] で、[Database] をクリックします。
[名前] ボックスに表示された既定のバックアップセット名をそのまま使用するか、または別のバックアップセット名を入力します。
オプションで、 [説明] ボックスに、バックアップセットの説明を入力します。
[ ディスク]、[ テープ ]、または [URL] をクリックして、バックアップ先の種類を選択します。 1 つのメディアセットを含む最大 64 台のディスクドライブまたはテープドライブのパスを選択するには、[ 追加] をクリックします。選択したパスは、 [バックアップ先] ボックスの一覧に表示されます。

バックアップ先を削除するには、バックアップ先を選択して [ 削除] をクリックします。バックアップ先の内容を表示するには、バックアップ先を選択して [ コンテンツ] をクリックします。
メディアオプションを表示または選択するには、[ページの選択] ウィンドウで [メディアオプション] をクリックします。
次のいずれかをクリックして、[ メディアの上書き ] オプションを選択します。
- 既存のメディアセットにバックアップする
  
  このオプションでは、[既存の バックアップセットに追加] または [ 既存のすべてのバックアップセットを上書きする] をクリックします。詳細については、「メディアセット、メディアファミリ、およびバックアップセット (SQL Server)」を参照してください。
  
  必要に応じて、[ メディアセット名とバックアップセットの有効期限の確認 ] を選択して、バックアップ操作でメディアセットとバックアップセットの有効期限が切れる日時を確認します。
  
  必要に応じて、[ メディアセット 名] テキストボックスに名前を入力します。名前が指定されていない場合は、空白の名前を持つメディアセットが作成されます。メディアセット名を指定すると、メディア (テープまたはディスク) がチェックされ、実際の名前がここで入力した名前と一致するかどうかが確認されます。
  
  重要
  
  [全般] ページでバックアップ先として URL を選択した場合、このオプションは無効になります。詳細については、「データベースのバックアップ (メディアオプションページ)」を参照してください。
  
  暗号化を使用する場合は、このオプションを選択しないでください。このオプションを選択すると、[ バックアップオプション] ページの暗号化オプションが無効になります。既存のバックアップセットに追加する場合、暗号化はサポートされません。
- 新しいメディアセットにバックアップし、既存のすべてのバックアップセットを消去する
  
  このオプションでは、[ 新しいメディアセット 名] テキストボックスに名前を入力し、必要に応じて、[ 新しいメディアセットの説明 ] テキストボックスにメディアセットを記述します。
  
  重要
  
  [全般] ページで URL を選択した場合、このオプションは無効になります。これらのアクションは、Azure Storage にバックアップする場合はサポートされません。
[ 信頼性 ] セクションで、必要に応じて次のことを確認します。
- 完了したら、バックアップを確認します。
- メディアに書き込む前にチェックサムを実行し、必要に応じてチェックサム エラーで続行します。チェックサムの詳細については、「バックアップと復元中に発生する可能性のあるメディアエラー (SQL Server)」を参照してください。
テープドライブにバックアップする場合 ([全般] ページの [宛先] セクションで指定されているとおり)、[バックアップ後にテープをアンロードする] オプションがアクティブになります。このオプションをクリックすると、[ アンロードする前にテープを巻き戻す ]オプションが有効になります。

注

トランザクションログをバックアップする場合を除き、[トランザクションログ] セクションのオプションは非アクティブです ([全般] ページの [バックアップの種類] セクションで指定)。
バックアップオプションを表示または選択するには、[ページの選択] ウィンドウで [バックアップオプション] をクリックします。
バックアップセットの有効期限が切れ、有効期限データの検証を明示的にスキップせずに上書きできるタイミングを指定します。
- 特定の日数後にバックアップセットの有効期限を切にするには、[ 後 ] (既定のオプション) をクリックし、セットの作成後にセットの有効期限が切れる日数を入力します。この値は 0 ~ 99999 日です。値が 0 日の場合は、バックアップセットの有効期限が切れないことを意味します。
  
  既定値は、[サーバーのプロパティ] ダイアログボックス ([データベース設定] ページ) の [バックアップメディアの既定の保持期間 (日数)] オプションで設定されます。これにアクセスするには、オブジェクトエクスプローラーでサーバー名を右クリックし、プロパティを選択します。をクリックし 、[データベース設定] ページを 選択します。
- バックアップセットの有効期限を特定の日付に設定するには、[ オン] をクリックし、セットの有効期限を入力します。
  
  バックアップの有効期限の詳細については、「 BACKUP (Transact-SQL)」を参照してください。
SQL Server 2008 Enterprise 以降では、バックアップ圧縮がサポートされています。既定では、バックアップが圧縮されるかどうかは、 バックアップ圧縮の既定 のサーバー構成オプションの値によって異なります。ただし、現在のサーバーレベルの既定値に関係なく、[バックアップの圧縮] をオンにしてバックアップを圧縮できます。また、[ バックアップを 圧縮しない] をオンにすることで圧縮を防ぐことができます。

現在のバックアップ圧縮の既定値を表示または変更するには
- バックアップ圧縮の既定のサーバー構成オプションを表示または構成する
バックアップに暗号化を使用するかどうかを指定します。暗号化手順に使用する暗号化アルゴリズムを選択し、既存の証明書または非対称キーの一覧から証明書または非対称キーを指定します。暗号化は SQL Server 2014 以降でサポートされています。暗号化オプションの詳細については、「データベースのバックアップ (バックアップオプションページ)」を参照してください。

注

または、メンテナンスプランウィザードを使用してデータベースバックアップを作成することもできます。

Transact-SQL の使用

データベースの完全バックアップを作成するには

BACKUP DATABASE ステートメントを実行して、次を指定してデータベースの完全バックアップを作成します。

バックアップするデータベースの名前。
データベースの完全バックアップが書き込まれるバックアップデバイス。

データベースの完全バックアップの基本的な Transact-SQL 構文は次のとおりです。

データベースをバックアップ データベース

to backup_device [ ,...n ]

[ WITH with_options [ ,...o ] ;

選択肢	説明
データベース	バックアップするデータベースです。
backup_device [ ,...n ]	バックアップ操作に使用する 1 ~ 64 個のバックアップデバイスの一覧を指定します。物理バックアップデバイスを指定することも、既に定義されている場合は、対応する論理バックアップデバイスを指定することもできます。物理バックアップデバイスを指定するには、DISK または TAPE オプションを使用します。 { DISK \|TAPE } =physical_backup_device_name 詳細については、「バックアップデバイス (SQL Server)」を参照してください。
WITH with_options [ ,...o ]	必要に応じて、1 つ以上の追加オプション o を指定します。オプション付きの基本の一部については、手順 2 を参照してください。

必要に応じて、1 つ以上の WITH オプションを指定します。ここでは、いくつかの基本的な WITH オプションについて説明します。すべての WITH オプションの詳細については、 BACKUP (Transact-SQL) を参照してください。
- オプション付き基本バックアップセット:
  
  { COMPRESSION |NO_COMPRESSION }
  SQL Server 2008 Enterprise 以降でのみ、このバックアップでバックアップ圧縮を実行するかどうかを指定し、サーバーレベルの既定値をオーバーライドします。
  
  暗号化 (アルゴリズム、サーバー証明書 |非対称キー)
  SQL Server 2014 以降でのみ、使用する暗号化アルゴリズムと、暗号化をセキュリティで保護するために使用する証明書または非対称キーを指定します。
  
  DESCRIPTION = { 'text' | @text_variable }
  バックアップセットを説明する自由形式のテキストを指定します。文字列の長さは最大 255 文字です。
  
  NAME = { backup_set_name | @backup_set_name_var }
  バックアップセットの名前を指定します。名前の長さは最大 128 文字です。 NAME を指定しないと、名前は空白になります。
- オプション付きの基本バックアップセット
  
  既定では、BACKUP は既存のメディアセットにバックアップを追加し、既存のバックアップセットを保持します。これを明示的に指定するには、NOINIT オプションを使用します。既存のバックアップセットへの追加の詳細については、「メディアセット、メディアファミリ、およびバックアップセット (SQL Server)」を参照してください。
  
  または、バックアップメディアをフォーマットするには、FORMAT オプションを使用します。
  
  FORMAT [ , MEDIANAME**=** { media_name | @media_name_variable } ] [ , MEDIADESCRIPTION = { text | @text_variable } ]
  初めてメディアを使用する場合、または既存のすべてのデータを上書きする場合は、FORMAT 句を使用します。必要に応じて、新しいメディアにメディア名と説明を割り当てます。
  
  重要
  
  BACKUP ステートメントの FORMAT 句を使用する場合は、バックアップメディアに以前に格納されていたバックアップが破棄されるため、細心の注意を払ってください。

例 (Transact-SQL)

ある。ディスクデバイスへのバックアップ

次の例では、FORMATを使用して新しいメディアセットを作成することで、AdventureWorks2012 データベース全体をディスクにバックアップします。

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'  
   WITH FORMAT,  
      MEDIANAME = 'Z_SQLServerBackups',  
      NAME = 'Full Backup of AdventureWorks2012';  
GO

B. テープデバイスへのバックアップ

次の例では、 AdventureWorks2012データベース全体をテープにバックアップし、バックアップを前のバックアップに追加します。

USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
   TO TAPE = '\\.\Tape0'  
   WITH NOINIT,  
      NAME = 'Full Backup of AdventureWorks2012';  
GO

C. 論理テープデバイスにバックアップする

次の例では、テープドライブの論理バックアップデバイスを作成します。次に、 AdventureWorks2012 データベース全体をそのデバイスにバックアップします。

-- Create a logical backup device,   
-- AdventureWorks2012_Bak_Tape, for tape device \\.\tape0.  
USE master;  
GO  
EXEC sp_addumpdevice 'tape', 'AdventureWorks2012_Bak_Tape', '\\.\tape0'; USE AdventureWorks2012;  
GO  
BACKUP DATABASE AdventureWorks2012  
   TO AdventureWorks2012_Bak_Tape  
   WITH FORMAT,  
      MEDIANAME = 'AdventureWorks2012_Bak_Tape',  
      MEDIADESCRIPTION = '\\.\tape0',   
      NAME = 'Full Backup of AdventureWorks2012';  
GO

PowerShell の使用

Backup-SqlDatabase コマンドレットを使用します。これがデータベースの完全バックアップであることを明示的に示すには、既定値の Database で -BackupAction パラメーターを指定します。このパラメーターは、データベースの完全バックアップでは省略可能です。

次の例では、サーバーインスタンス Computer\Instanceの既定のバックアップ場所に、MyDB データベースの完全なデータベースバックアップを作成します。必要に応じて、この例では -BackupAction Databaseを指定します。
```
Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database  
```

SQL Server PowerShell プロバイダーを設定して使用するには

SQL Server PowerShell プロバイダー

こちらもご覧ください

バックアップの概要 (SQL Server)
トランザクションログバックアップ (SQL Server)
メディアセット、メディアファミリ、およびバックアップセット (SQL Server)
sp_addumpdevice (Transact-SQL)
BACKUP (Transact-SQL)
データベースのバックアップ ([全般] ページ)
データベースのバックアップ ([バックアップオプション] ページ)
差分バックアップ (SQL Server)
データベースの完全バックアップ (SQL Server)