データベースの完全バックアップの作成

適用対象:yesSQL Server (サポートされているすべてのバージョン)

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

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

制限事項と制約事項

  • BACKUP ステートメントは、明示的なトランザクションまたは暗黙のトランザクションでは使用できません。
  • 新しいバージョンの SQL Server によって作成されたバックアップは、以前のバージョンの SQL Serverでは復元できません。

バックアップの概念とタスクの概要と詳細については、続行する前にバックアップの概要 (SQL Server) を参照してください。

Recommendations

  • データベース サイズが大きくなると、データベースの完全バックアップにかかる時間は長くなり、必要な記憶領域も増加します。 大規模なデータベースでは、一連のデータベースの差分バックアップを使用してデータベースの完全バックアップを補完することを検討してください。
  • データベースの完全バックアップのサイズは、 sp_spaceused システム ストアド プロシージャを使用して推計します。
  • 既定では、バックアップ操作が成功するたびに、 SQL Server エラー ログおよびシステム イベント ログにエントリが 1 つ追加されます。 バックアップを頻繁に行うと、成功メッセージがすぐに蓄積され、エラー ログが大きくなり、他のメッセージを探すのが困難になります。 そのような場合、これらのエントリに依存するスクリプトがなければ、トレース フラグ 3226 を使用することによってこれらのバックアップ ログ エントリを除外できます。 詳細については、「トレース フラグ (Transact-SQL)」を参照してください。

セキュリティ

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

SQL Server 2012 (11.x) 以降では、バックアップの作成で 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 スクリプトを生成できます。

  1. オブジェクト エクスプローラーで適切な Microsoft SQL Server データベース エンジン のインスタンスに接続した後、サーバー ツリーを展開します。

  2. [データベース] を展開し、ユーザー データベースを選択するか、または [システム データベース] を展開してシステム データベースを選択します。

  3. バックアップするデータベースを右クリックし、 [タスク] をポイントしてから、 [バックアップ] を選択します。

  4. [データベースのバックアップ] ダイアログボックスで、選択したデータベースがドロップダウン リストに表示されます (これは、サーバー上の他の任意のデータベースに変更できます)。

  5. [バックアップの種類] ドロップダウン リストで、バックアップの種類 (既定値は [完全] ) を選択します。

    重要

    差分バックアップまたはトランザクション ログ バックアップを実行するには、事前にデータベースの完全バックアップを少なくとも 1 回実行する必要があります。

  6. [バックアップ コンポーネント][データベース] を選択します。

  7. [バックアップ先] セクションで、バックアップ ファイルの既定の場所を確認します (../mssql/data フォルダー内)。

    [バックアップ先] ドロップダウン リストを使用して、別のデバイスを選択できます。 [追加] を選択して、バックアップ オブジェクトやバックアップ先を追加します。 バックアップの速度を向上させるために、バックアップ セットを複数のファイルにまたがってストライプすることができます。

    バックアップ先を削除するには、バックアップ先を選択して [削除] を選択します。 既存のバックアップ先の内容を表示するには、バックアップ先を選択して [内容] を選択します。

  8. (省略可能) [メディア オプション][バックアップ オプション] のページで、その他の使用可能な設定を確認します。

    さまざまなバックアップ オプションの詳細については、[全般] ページ[メディア オプション] ページ[バックアップ オプション] ページを参照してください。

  9. [OK] を選択してバックアップを開始します。

  10. バックアップが正常に完了したら、 [OK] を選択して SQL Server Management Studio のダイアログ ボックスを閉じます。

関連情報

  • データベースの完全バックアップを作成したら、データベースの差分バックアップまたはトランザクション ログ バックアップを作成できます。

  • (省略可能) [コピーのみのバックアップ] チェック ボックスをオンにして、コピーのみのバックアップを作成することもできます。 コピーのみのバックアップは、従来の SQL Server バックアップのシーケンスから独立した SQL Server バックアップです。 詳細については、「コピー専用バックアップ (SQL Server)」を参照してください。 コピーのみのバックアップは、 [差分] バックアップの種類には使用できません。

  • URL にバックアップする場合は、 [メディア オプション] ページで [メディアに上書きします] オプションが無効にされます。

次の例では、次の Transact-SQL コードを使用してテスト データベースを作成します。

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A. 既定の場所のディスクへの完全バックアップ

この例では、SQLTestDB データベースを既定のバックアップ場所にあるディスクにバックアップします。

  1. オブジェクト エクスプローラーで適切な Microsoft SQL Server データベース エンジン のインスタンスに接続した後、サーバー ツリーを展開します。

  2. [データベース] を展開して SQLTestDB を右クリックし、 [タスク] をポイントしてから [バックアップ] を選択します。

  3. [OK] を選択します。

  4. バックアップが正常に完了したら、 [OK] を選択して SQL Server Management Studio のダイアログ ボックスを閉じます。

Take SQL backup

B. 既定以外の場所のディスクへの完全バックアップ

この例では、ご自分で選択した場所にあるディスクに SQLTestDB データベースがバックアップされます。

  1. オブジェクト エクスプローラーで適切な Microsoft SQL Server データベース エンジン のインスタンスに接続した後、サーバー ツリーを展開します。

  2. [データベース] を展開して SQLTestDB を右クリックし、 [タスク] をポイントしてから [バックアップ] を選択します。

  3. [全般] ページの [バックアップ先] セクションで、 [バックアップ先] ドロップダウン リストから [ディスク] を選択します。

  4. 既存のバックアップ ファイルがすべて削除されるまで、 [削除] を選択します。

  5. [追加] を選択すると、 [バックアップ先の選択] ダイアログ ボックスが開きます。

  6. [ファイル名] テキスト ボックスに有効なパスとファイル名を入力し、拡張子として .bak を使用することで、このファイルの分類を簡略化します。

  7. [OK] を選択してから、もう一度 [OK] を選択してバックアップを開始します。

  8. バックアップが正常に完了したら、 [OK] を選択して SQL Server Management Studio のダイアログ ボックスを閉じます。

Change DB location

C. 暗号化されたバックアップの作成

この例では、SQLTestDB データベースを暗号化して既定のバックアップ場所にバックアップします。

  1. オブジェクト エクスプローラーで適切な Microsoft SQL Server データベース エンジン のインスタンスに接続した後、サーバー ツリーを展開します。

  2. [データベース][システム データベース] の順に展開し、master を右クリックして [新しいクエリ] を選択します。これにより、ご利用の SQLTestDB データベースに接続した状態でクエリ ウィンドウが開きます。

  3. 次のコマンドを実行して、master データベース内にデータベース マスター キー証明書を作成します。

    -- Create the master key
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe';  
    
    -- If the master key already exists, open it in the same session that you create the certificate (see next step)
    OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hxJ#KL95234nl0zBe'
    
    -- Create the certificate encrypted by the master key
    CREATE CERTIFICATE MyCertificate
    WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20201031';  
    
  4. オブジェクト エクスプローラー[データベース] ノードで、SQLTestDB を右クリックし、 [タスク] をポイントしてから [バックアップ] を選択します。

  5. [メディア オプション] ページの [メディアを上書きする] セクションで、 [新しいメディア セットにバックアップし、すべての既存のバックアップ セットを消去する] をオンにします。

  6. [バックアップ オプション] ページの [暗号化] セクションで、 [バックアップを暗号化する] チェック ボックスをオンにします。

  7. [アルゴリズム] ボックスの一覧から、 [AES 256] を選択します。

  8. [証明書または非対称キー] ボックスの一覧で、 MyCertificateを選択します。

  9. [OK] を選択します。

Encrypted backup

D. Azure BLOB ストレージ サービスへのバックアップ

この例では、Azure Blob Storage サービスへの SQLTestDB のデータベースの完全バックアップを作成します。 例では、BLOB コンテナーを含むストレージ アカウントを既に用意していることを前提としています。 例では、共有アクセス署名が自動的に作成されます。コンテナーに既存の共有アクセス署名がある場合、この例は失敗します。

ストレージ アカウントに Azure BLOB コンテナーがない場合は、作成してから続行してください。 汎用ストレージ アカウントの作成に関する記事と「コンテナーを作成する」を参照してください。

  1. オブジェクト エクスプローラーで適切な Microsoft SQL Server データベース エンジン のインスタンスに接続した後、サーバー ツリーを展開します。

  2. [データベース] を展開して SQLTestDB を右クリックし、 [タスク] をポイントしてから [バックアップ] を選択します。

  3. [全般] ページの [宛先] セクションで、 [バックアップ先] ドロップダウン リストから [URL] を選択します。

  4. [追加] を選択すると、 [バックアップ先の選択] ダイアログ ボックスが開きます。

  5. SQL Server Management Studio で使用する Azure ストレージ コンテナーを以前に登録したことがある場合は、それを選択します。 それ以外の場合は、 [新しいコンテナー] を選択して新しいコンテナーを登録します。

  6. [Microsoft サブスクリプションへの接続] ダイアログ ボックスで、ご利用のアカウントにサインインします。

  7. [ストレージ アカウントの選択] ドロップダウン テキスト ボックスで、ご利用のストレージ アカウントを選択します。

  8. [BLOB コンテナーの選択] ドロップダウン テキスト ボックスで、ご利用の BLOB コンテナーを選択します。

  9. [Shared Access Policy の有効期限] ドロップダウン カレンダー ボックスで、この例で作成した共有アクセスポリシーの有効期限を選択します。

  10. SQL Server Management Studio で [資格情報の作成] を選択して、共有アクセス署名と資格情報を生成します。

  11. [OK] を選択して、 [Microsoft サブスクリプションへの接続] ダイアログ ボックスを閉じます。

  12. [バックアップ ファイル] テキスト ボックスで、バックアップ ファイルの名前を変更します (省略可能)。

  13. [OK] を選択して [バックアップ先の選択] ダイアログ ボックスを閉じます。

  14. [OK] を選択してバックアップを開始します。

  15. バックアップが正常に完了したら、 [OK] を選択して SQL Server Management Studio のダイアログ ボックスを閉じます。

Transact-SQL の使用

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

  • バックアップするデータベースの名前。
  • データベースの完全バックアップを書き込むバックアップ デバイス。

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

BACKUP DATABASE database TO backup_device [ , ...n ] [ WITH with_options [ , ...o ] ] ;

オプション 説明
database バックアップするデータベースです。
backup_device [ , ...n ] バックアップ操作に使用する 1 ~ 64 個のバックアップ デバイスの一覧を指定します。 物理バックアップ デバイスを指定したり、対応する論理バックアップ デバイス (既に定義されている場合) を指定したりできます。 物理バックアップ デバイスを指定するには、DISK オプションまたは TAPE オプションを使用します。

{ DISK | TAPE } =physical_backup_device_name

詳細については、「バックアップ デバイス (SQL Server)」を参照してください。
WITH with_options [ , ...o ] 1 つまたは複数のオプション o を指定するために使用します。 基本的な with オプションについては、手順 2. を参照してください。

必要に応じて、1 つ以上の WITH オプションを指定します。 ここでは、一部の基本的な WITH オプションについて説明します。 すべての WITH オプションの詳細については、「BACKUP (Transact-SQL)」を参照してください。

基本的なバックアップ セットの WITH オプション:

  • { COMPRESSION | NO_COMPRESSION } :SQL Server 2008 Enterprise 以降でのみ、このバックアップで バックアップの圧縮 を実行するかどうかを指定し、サーバー レベルの既定値をオーバーライドできます。
  • ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY) : SQL Server 2014 以降でのみ、使用する暗号化アルゴリズムと暗号化の保護に使用する証明書または非対称キーを指定します。
  • 説明= { '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 コードを使用してテスト データベースを作成します。

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT GETDATE()
)
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

A. ディスク デバイスへのバックアップ

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

USE SQLTestDB;
GO
BACKUP DATABASE SQLTestDB
TO DISK = 'c:\tmp\SQLTestDB.bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of SQLTestDB';
GO

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

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

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

C. 論理テープ デバイスへのバックアップ

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

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

PowerShell の使用

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

注意

これらの例では、SqlServer モジュールが必要です。 それがインストールされているかどうかを判断するには、Get-Module -Name SqlServer を実行します。 インストールするには、PowerShell の管理者セッションで Install-Module -Name SqlServer を実行します。

詳細については、「 SQL Server PowerShell プロバイダー」を参照してください。

重要

SQL Server Management Studio 内から PowerShell ウィンドウを開いて SQL Server のインストールに接続する場合、PowerShell と SQL Server インスタンスとの接続の確立には SSMS 内の資格情報が自動的に使用されるので、資格情報の部分は省略できます。

A. 完全バックアップ (ローカル)

次の例では、 <myDatabase> データベースの完全なバックアップを、サーバー インスタンス Computer\Instanceの既定のバックアップ場所に作成します。 オプションで、この例では -BackupAction Databaseを指定します。

完全な構文の例については、「Backup-SqlDatabase」を参照してください。

$credential = Get-Credential

Backup-SqlDatabase -ServerInstance Computer[\Instance] -Database <myDatabase> -BackupAction Database -Credential $credential

B. Azure への完全バックアップ

次の例では、Azure Blob Storage サービスに <myDatabase> インスタンスのデータベース <myServer> の完全バックアップを作成します。 保存されたアクセス ポリシーは読み取り、書き込み、および一覧表示権で作成されています。 SQL Server 資格情報 https://<myStorageAccount>.blob.core.windows.net/<myContainer>は、保存されたアクセス ポリシーに関連付けられている Shared Access Signature を使用して作成されています。 PowerShell コマンドでは BackupFile パラメーターを使用して、場所 (URL) とバックアップ ファイル名を指定します。

$credential = Get-Credential
$container = 'https://<myStorageAccount>blob.core.windows.net/<myContainer>'
$fileName = '<myDatabase>.bak'
$server = '<myServer>'
$database = '<myDatabase>'
$backupFile = $container + '/' + $fileName

Backup-SqlDatabase -ServerInstance $server -Database $database -BackupFile $backupFile -Credential $credential

関連タスク

関連項目