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

[アーティクル]
01/09/2024

適用対象:SQL Server

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

詳細については、「Azure Blob Storage を使用した SQL Server のバックアップと復元」と「SQL Server の URL へのバックアップ」をご覧ください。

制限事項と制約事項

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

バックアップの概念とタスクに関する概要および詳細については、先へ進む前に「Backup Overview (SQL Server)」を参照してください。

推奨事項

データベースサイズが大きくなると、データベースの完全バックアップにかかる時間は長くなり、必要な記憶領域も増加します。大規模なデータベースでは、一連のデータベースの差分バックアップを使用してデータベースの完全バックアップを補完することを検討してください。
データベースの完全バックアップのサイズは、 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 を使用する

Note

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

オブジェクトエクスプローラーで Microsoft SQL Server データベースエンジンの適切なインスタンスに接続した後、サーバーツリーを展開します。
[データベース]を展開し、ユーザーデータベースを選択するか、または [システムデータベース] を展開してシステムデータベースを選択します。
バックアップするデータベースを右クリックし、[タスク] をポイントしてから、[バックアップ...] を選択します。
[データベースのバックアップ] ダイアログボックスで、選択したデータベースがドロップダウンリストに表示されます (これは、サーバー上の他の任意のデータベースに変更できます)。
[バックアップの種類] ドロップダウンリストで、バックアップの種類 (既定値は [完全]) を選択します。

重要

差分バックアップまたはトランザクションログバックアップを実行するには、事前にデータベースの完全バックアップを少なくとも 1 回実行する必要があります。
[バックアップコンポーネント] で [データベース] を選択します。
[バックアップ先] セクションで、バックアップファイルの既定の場所を確認します (../mssql/data フォルダー内)。

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

バックアップ先を削除するには、バックアップ先を選択して [削除] を選択します。既存のバックアップ先の内容を表示するには、バックアップ先を選択して [内容] を選択します。
(省略可能) [メディアオプション] と [バックアップオプション] のページで、その他の使用可能な設定を確認します。

さまざまなバックアップオプションの詳細については、[全般] ページ、[メディアオプション] ページ、[バックアップオプション] ページを参照してください。
[OK] を選択してバックアップを開始します。
バックアップが正常に完了したら、[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 データベースを既定のバックアップ場所にあるディスクにバックアップします。

オブジェクトエクスプローラーで Microsoft SQL Server データベースエンジンの適切なインスタンスに接続した後、サーバーツリーを展開します。
[データベース] を展開して SQLTestDB を右クリックし、[タスク] をポイントしてから [バックアップ] を選択します。
[OK] を選択します。
バックアップが正常に完了したら、[OK] を選択して SQL Server Management Studio のダイアログボックスを閉じます。

Take SQL backup

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

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

オブジェクトエクスプローラーで Microsoft SQL Server データベースエンジンの適切なインスタンスに接続した後、サーバーツリーを展開します。
[データベース] を展開して SQLTestDB を右クリックし、[タスク] をポイントしてから [バックアップ] を選択します。
[全般] ページの [バックアップ先] セクションで、 [バックアップ先] ドロップダウンリストから [ディスク] を選択します。
既存のバックアップファイルがすべて削除されるまで、[削除] を選択します。
[追加] を選択すると、[バックアップ先の選択] ダイアログボックスが開きます。
[ファイル名] テキストボックスに有効なパスとファイル名を入力し、拡張子として .bak を使用することで、このファイルの分類を簡略化します。
[OK] を選択してから、もう一度 [OK] を選択してバックアップを開始します。
バックアップが正常に完了したら、[OK] を選択して SQL Server Management Studio のダイアログボックスを閉じます。

Change DB location

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

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

オブジェクトエクスプローラーで Microsoft SQL Server データベースエンジンの適切なインスタンスに接続した後、サーバーツリーを展開します。
[データベース]、[システムデータベース] の順に展開し、master を右クリックして [新しいクエリ] を選択します。これにより、ご利用の SQLTestDB データベースに接続した状態でクエリウィンドウが開きます。

次のコマンドを実行して、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';

オブジェクトエクスプローラーの [データベース] ノードで、SQLTestDB を右クリックし、[タスク] をポイントしてから [バックアップ] を選択します。
[メディアオプション] ページの [メディアを上書きする] セクションで、[新しいメディアセットにバックアップし、すべての既存のバックアップセットを消去する] をオンにします。
[バックアップオプション] ページの [暗号化] セクションで、 [バックアップを暗号化する] チェックボックスをオンにします。
[アルゴリズム] ボックスの一覧から、[AES 256]を選択します。
[証明書または非対称キー] ボックスの一覧で、 MyCertificateを選択します。
[OK] を選択します。

Encrypted backup

D. Azure BLOB Storage へバックアップする

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

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

オブジェクトエクスプローラーで Microsoft SQL Server データベースエンジンの適切なインスタンスに接続した後、サーバーツリーを展開します。
[データベース] を展開して SQLTestDB を右クリックし、[タスク] をポイントしてから [バックアップ] を選択します。
[全般] ページの [宛先] セクションで、 [バックアップ先] ドロップダウンリストから [URL] を選択します。
[追加] を選択すると、[バックアップ先の選択] ダイアログボックスが開きます。
SQL Server Management Studio で使用する Azure ストレージコンテナーを以前に登録したことがある場合は、それを選択します。それ以外の場合は、[新しいコンテナー] を選択して新しいコンテナーを登録します。
[Microsoft サブスクリプションへの接続] ダイアログボックスで、ご利用のアカウントにサインインします。
[ストレージアカウントの選択] ドロップダウンテキストボックスで、ご利用のストレージアカウントを選択します。
[BLOB コンテナーの選択] ドロップダウンテキストボックスで、ご利用の BLOB コンテナーを選択します。
[Shared Access Policy の有効期限] ドロップダウンカレンダーボックスで、この例で作成した共有アクセスポリシーの有効期限を選択します。
SQL Server Management Studio で [資格情報の作成] を選択して、共有アクセス署名と資格情報を生成します。
[OK] を選択して、[Microsoft サブスクリプションへの接続] ダイアログボックスを閉じます。
[バックアップファイル] テキストボックスで、バックアップファイルの名前を変更します (省略可能)。
[OK] を選択して [バックアップ先の選択] ダイアログボックスを閉じます。
[OK] を選択してバックアップを開始します。
バックアップが正常に完了したら、[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 (10.0.x) Enterprise 以降のバージョンのみで、サーバーレベルの既定値をオーバーライドして、このバックアップに対してバックアップの圧縮を実行するかどうかを指定します。
ENCRYPTION (ALGORITHM, SERVER CERTIFICATE | ASYMMETRIC KEY): 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 コードを使用してテストデータベースを作成します。

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 を指定します。このパラメーターは、データベースの完全バックアップでは省略可能です。

Note

これらの例では、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 への完全バックアップ

次の例では、<myServer> インスタンスのデータベース <myDatabase> の完全バックアップを Azure Blob Storage に作成します。保存されたアクセスポリシーは読み取り、書き込み、および一覧表示権で作成されています。 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

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

制限事項と制約事項

推奨事項

セキュリティ

アクセス許可

SQL Server Management Studio を使用する

追加情報

例

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

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

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

D. Azure BLOB Storage へバックアップする

Transact-SQL の使用

例

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

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

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

PowerShell の使用

例

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

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

関連タスク

フィードバック

フィードバック

その他のリソース

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

制限事項と制約事項

推奨事項

セキュリティ

アクセス許可

SQL Server Management Studio を使用する

追加情報

例

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

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

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

D. Azure BLOB Storage へバックアップする

Transact-SQL の使用

例

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

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

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

PowerShell の使用

例

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

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

関連タスク

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

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

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

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