Info2softは、ウェブサイトでより快適で適切な閲覧体験を提供するためにCookieを使用しています。 プライバシーポリシー
Loading...
By:
SQL Serverデータベースのバックアップ作成は、データベース管理者やITチームにとって最も重要な業務の一つです。SQL Server Management Studio（SSMS）にはGUIによるバックアップ作成機能が備わっていますが、多くの技術者はT-SQLクエリを活用しています。実行速度が速いだけでなく、自動化や保守スクリプトへの組み込みが容易なためです。
クエリを使用することで管理者はバックアップ処理を細かく制御できるため、定時実行ジョブ、災害復旧計画、エンタープライズ規模のデータベース管理に適しています。
本ガイドでは、T-SQLコマンドによるSQL Serverデータベースのバックアップ手法を解説します。完全バックアップ、差分バックアップ、トランザクションログバックアップ、バックアップ検証、データベース復元の手順を網羅しています。初心者から経験豊富なDBAまで活用でき、信頼性の高いSQL Serverバックアップ方針を構築できます。
バックアップコマンドを学ぶ前に、多くのDBAがGUIツールではなくT-SQLを採用する理由を理解しましょう。
クエリを実行するだけでバックアップ処理を起動でき、SSMSの複数メニューを操作する手間を省けます。
T-SQLバックアップコマンドはSQL Serverエージェントジョブ、PowerShellスクリプト、エンタープライズ向け自動化ワークフローに組み込めます。
統一されたスクリプトを使用することで、複数のデータベースのバックアップ管理が大幅に簡素化されます。
バックアップを作成する前に、環境が以下の条件を満たしているか確認してください。
バックアップコマンドを実行するアカウントに十分な権限が付与されている必要があります。
以下のクエリを実行します：
SELECT IS_SRVROLEMEMBER('sysadmin');実行結果が1の場合、該当アカウントにsysadmin権限が付与されています。
出力先のフォルダが事前に作成されていることを確認します。
D:\SQLBackups\追加で以下の点を確認します：
SQL Serverサービスアカウントに対象フォルダへの書き込み権限が付与されている
保存先ドライブに十分な空き容量が確保されている
バックアップストレージの容量を定期的に監視する
データベースの容量を事前に把握することで、ストレージ不足によるバックアップ失敗を回避できます。
以下のコマンドを実行します：
EXEC sp_spaceused;バックアップ保存先を決定する前に、出力されたデータベースサイズを確認してください。
BACKUP DATABASE構文はSQL Serverデータベースのバックアップを作成する基本コマンドです。
段階的な実行手順を解説します。
完全バックアップには、テーブル、インデックス、ストアドプロシージャ、全データを含むデータベース全体の情報が格納されます。
完全バックアップを作成するには、以下のクエリを実行します：
BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH
FORMAT,
INIT,
NAME = 'SalesDB Full Backup';クエリの各オプション解説
BACKUP DATABASE SalesDB
バックアップ対象のデータベースを指定します。
TO DISK
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak'バックアップファイルの保存パスとファイル名を定義します。
FORMAT
新しいメディアセットを作成し、過去のバックアップヘッダーを削除します。
INIT
同名のバックアップファイルが存在する場合、上書きします。
NAME
バックアップセットに識別用の名称を付与します。
実行成功時の出力結果
コマンド実行後、SQL Serverは以下のようなメッセージを返します：
BACKUP DATABASE successfully processed.指定したディレクトリにバックアップファイルが作成されます。
バックアップ圧縮機能を使用することで、必要なストレージ容量を削減し、バックアップ処理の速度を向上できます。
以下のコマンドを実行します：
BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Compressed.bak'
WITH COMPRESSION;圧縮機能のメリット
バックアップファイルのサイズ縮小
ストレージコストの削減
ネットワーク転送速度の向上
バックアップファイルの管理が簡素化
本番環境のデータベースでは圧縮オプションの使用を強く推奨します。
バックアップファイルを作成するだけでは不十分です。SQL Serverがファイルを正常に読み込めるか必ず検証してください。
以下のコマンドを実行します：
RESTORE VERIFYONLY
FROM DISK = 'D:\SQLBackups\SalesDB_Full.bak';正常時の出力結果
バックアップファイルに破損がない場合、SQL Serverは以下のメッセージを返します：
The backup set on file 1 is valid.検証作業が重要な理由
多くの管理者はバックアップジョブが完了した時点で復元可能だと判断しがちです。しかしストレージ障害、ファイル破損、権限不備などにより、バックアップファイルが使用不可になるケースが存在します。
RESTORE VERIFYONLYを実行することで、災害発生前に潜在的な不具合を事前に検知できます。
SQL ServerはMSDBシステムデータベース内に全てのバックアップ実行履歴を保存しています。
以下のクエリを実行して履歴を参照します：
SELECT
bs.database_name,
bs.backup_start_date,
bs.backup_finish_date,
bs.type,
bmf.physical_device_name
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id
ORDER BY bs.backup_finish_date DESC;| バックアップ種別コード | 意味 |
|---|---|
| D | 完全バックアップ |
| I | 差分バックアップ |
| L | トランザクションログバックアップ |
このクエリはバックアップ業務の監査や、定時ジョブが正常に実行されているか確認する際に活用できます。
完全バックアップはデータを完全に保護できますが、ファイルサイズが大きく、作成に時間を要するという課題があります。
差分バックアップは直近の完全バックアップ以降に発生した変更点のみを記録します。
差分バックアップを作成するには以下のコマンドを実行します：
BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Diff.bak'
WITH DIFFERENTIAL;以下の運用例を参考にしてください：
| 曜日 | バックアップ種別 |
|---|---|
| 月曜日 | 完全バックアップ |
| 火曜日 | 差分バックアップ |
| 水曜日 | 差分バックアップ |
水曜日の差分バックアップには、月曜日の完全バックアップ作成後に発生した全てのデータ変更が格納されます。
正常に復元を完了させるには、以下2種類のファイルが必要です。
最新の完全バックアップファイル
最新の差分バックアップファイル
この運用方式により、バックアップファイルの容量を抑えつつ、迅速な復元を実現できます。
完全復旧モデルで運用されているデータベースにおいて、トランザクションログバックアップは必須です。
データ損失の範囲を最小限に抑え、任意時点復元を実現するために活用します。
以下のクエリを実行します：
SELECT
name,
recovery_model_desc
FROM sys.databases
WHERE name = 'SalesDB';実行結果にFULLと表示された場合、トランザクションログのバックアップを作成可能です。
以下のコマンドを実行します：
BACKUP LOG SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Log.trn';トランザクションログバックアップのメリット
直近の全トランザクションを記録
目標復旧時点（RPO）を短縮
任意時点復元に対応
トランザクションログファイルの肥大化を防止
運用事例
下記の運用ルールを設定した場合を想定します：
深夜0時に完全バックアップを実行
15分間隔でログバックアップを取得
14時07分にデータベース障害が発生
ログバックアップを活用することで、14時06分までのデータを復元でき、データ損失を最小限に抑えられます。
バックアップ作成は復旧プロセスの半分に過ぎません。障害発生時に備え、復元手順も習得しておく必要があります。
バックアップファイルからデータベースを復元するには、以下のコマンドを実行します：
RESTORE DATABASE SalesDB
FROM DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH REPLACE;コマンド解説
RESTORE DATABASE
復元対象のデータベースを指定します。
FROM DISK
復元元のバックアップファイルパスを指定します。
WITH REPLACE
既存の同名データベースを上書きして復元することを許可します。
復元作業を開始する前に以下の点を確認してください。
データベースへのアクティブな接続が存在しない
バックアップファイルが正常であることを事前検証済み
バックアップのSQL Serverバージョンと復元先環境が互換性を持つ
実行中のデータベースが上書きされる点を把握している
復元後のデータベース検証
復元完了後、以下のコマンドを実行してデータベースの整合性を確認します：
DBCC CHECKDB ('SalesDB');このコマンドはデータベース内部の不整合をチェックし、復元作業が正常に完了したか確認できます。
テストや学習用途では手動でクエリを実行する手法が活用できますが、本番環境では定時的に安定してバックアップを取得するため、自動化が不可欠です。
SQL Serverエージェントを使用することで人手を介さずバックアップジョブを実行できます。
SQL Server Management Studio（SSMS）の以下のメニューから操作します：
オブジェクトエクスプローラー
→ SQL Server エージェント
→ ジョブ
→ 新しいジョブ
ジョブ名に分かりやすい名称を設定します。例：
毎日完全データベースバックアップ
名称を明確にすることで、後の保守やトラブルシューティングが容易になります。
作成したジョブ内のステップタブを開き、新規ステップを作成します。
以下の設定を選択します：
種類：Transact-SQL スクリプト(T-SQL)
データベース：master
バックアップ用のT-SQLクエリを入力します：
BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH
COMPRESSION,
INIT;ステップ設定を保存します。
スケジュールタブを開き、新しい実行スケジュールを作成します。
環境別の推奨設定例は以下の通りです：
| 環境区分 | 推奨実行間隔 |
|---|---|
| 開発環境 | 毎日 |
| 中小企業本番環境 | 毎日完全バックアップ |
| 大企業本番環境 | 完全＋差分＋ログバックアップの組み合わせ |
設定例：
毎日23時に実行
または
毎日曜日午前1時に実行
最適なスケジュールは業務要件と許容可能なデータ損失時間に基づいて決定します。
定時実行に切り替える前に、手動でジョブを実行し動作確認を行います。
以下の項目を検証します：
バックアップファイルが正常に作成される
ジョブがエラーなく完了する
権限に関するエラーが発生しない
保存先に十分なストレージ容量が存在する
自動化によりバックアップの実行漏れを防ぎ、安定したデータ保護を実現できます。
環境の設定が正しくないと、単純なバックアップ操作でも失敗する可能性があります。
以下に、バックアップ関連の最も頻出するエラーとその解決策を記載します。
典型的なエラーメッセージ
Operating system error 5 (Access is denied).
原因
SQL Serverサービスアカウントに、出力先ディレクトリへの書き込み権限が付与されていません。
解決策
SQL Serverのサービスアカウントを確認します：
SELECT servicename, service_account
FROM sys.dm_server_services;
バックアップ先ディレクトリに書き込みアクセス権を付与し、バックアップを再実行します。
典型的なエラーメッセージ
Cannot open backup device.
Operating system error 3.
原因
指定したフォルダが存在しない、またはファイルパスが誤っています。
例：
D:\SQLBackups\
サーバー上に上記フォルダが作成されていない場合があります。
解決策
以下を確認します：
ディレクトリが存在するか
ドライブ文字が正しいか
SQL Serverが当該場所にアクセス可能か
典型的なエラーメッセージ
There is insufficient free space on disk volume.
原因
バックアップ保存先のストレージ空き容量が不足しています。
解決策
以下の対応を検討します：
古いバックアップファイルの削除
ストレージ容量の拡張
バックアップ圧縮の利用
バックアップ保存期間ポリシーの導入
典型的なエラーメッセージ
BACKUP LOG cannot be performed because there is no current database backup.
原因
トランザクションログバックアップを実行するには、事前に完全バックアップが必要です。
解決策
まず完全バックアップを作成します：
BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak';その後、ログバックアップを再度実行します。
バックアップの作成は重要ですが、障害発生時に確実に復旧を実現するためにはベストプラクティスの順守が不可欠です。
本番データベースと同一ディスクにバックアップを保存してはいけません。
ストレージ機器の故障が発生した場合、データベースとバックアップファイルの両方が消失する恐れがあります。
推奨する構成：
本番データベース
↓
専用バックアップストレージ
↓
オフサイトまたはクラウドストレージ
この構成は広く採用されている3-2-1バックアップ戦略に準拠しています。
多くの企業はバックアップの作成確認のみを行い、実際の復旧テストを実施していません。
定期的に以下の手順を実施します：
テストサーバーにバックアップを復元する
アプリケーションの動作を検証する
データの完全性を確認する
復元できないバックアップには保護効果がありません。
完全バックアップのみに依存すると、バックアップ実行時間と必要なストレージ容量が増大します。
一般的な運用方針：
| バックアップ種別 | 実行頻度 |
|---|---|
| 完全バックアップ | 毎週 |
| 差分バックアップ | 毎日 |
| ログバックアップ | 15～30分間隔 |
この手法により、復旧速度とストレージ効率のバランスを保てます。
バックアップジョブの失敗を放置してはいけません。
監視対象項目：
ジョブの失敗
バックアップ処理時間
ストレージ使用量
バックアップ完了ステータス
早期検知により、障害発生時の復旧トラブルを回避できます。
顧客情報、財務記録、規制対象データを含むバックアップには暗号化を有効にする必要があります。
例：
BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Encrypted.bak'
WITH ENCRYPTION
(
ALGORITHM = AES_256,
SERVER CERTIFICATE = BackupCertificate
);暗号化により、不正アクセスからバックアップファイルを保護します。
T-SQLはデータベースバックアップに高い柔軟性を提供します。しかし、環境が拡大するにつれ、手動でのバックアップ管理は困難になります。
頻出する課題：
企業では複数のサーバーに数十～数百台のデータベースを管理するケースが多いです。
各インスタンスごとにバックアップスクリプトを保守すると管理が複雑化します。
T-SQLスクリプトには、複数環境のバックアップ状況を確認する統合ダッシュボードが存在しません。
管理者はジョブ履歴やログを手動で確認する必要があります。
大規模環境の復旧には、多くの場合以下の作業が伴います：
適切なバックアップチェーンの特定
複数ファイルの復元
復旧データの整合性検証
重大な障害発生時には多大な時間を要します。
手動運用には以下のリスクが潜んでいます：
ファイルパスの誤記
バックアップジョブの漏れ
保存期間設定の誤り
バックアップ検証の未実施
環境規模が拡大するほどこれらの問題が発生しやすくなります。
業務上重要なデータベースを管理する企業にとって、バックアップの正常実行だけが課題ではありません。一元管理、監視、コンプライアンス対応、復旧速度も同等に重要です。
ここでi2Backupが従来のSQL Serverバックアップ手法を補完します。
i2Backupは、単一の管理プラットフォームからデータベース、物理サーバー、仮想マシン、アプリケーション、クラウドワークロードを保護するために設計されたエンタープライズ向けバックアップ・復旧ソリューションです。
手動で保守するバックアップスクリプトに完全依存する代わりに、管理者は統合されたUIからバックアップ操作を管理できます。
自動バックアップスケジュール機能
バックアップタスクを一元的にスケジュール・管理でき、管理者の作業負担を削減します。
一元管理機能
各環境ごとに個別スクリプトを用意することなく、複数のSQL Serverインスタンスの状況を一括確認可能です。
増分バックアップ機能
変更されたデータのみをバックアップすることで、ストレージ消費量を削減し、バックアップ効率を向上させます。
高速な復旧処理
簡略化された復旧ワークフローにより、障害・データ損失発生時のダウンタイムを短縮します。
統合データ保護
SQL Serverに加え、同一プラットフォームから仮想マシン、ファイルシステム、その他重要な業務基盤の保護が可能です。
下表は、専用バックアッププラットフォームが追加の価値を提供するケースを比較したものです。
| 運用環境 | 手動T-SQLクエリ | i2Backup |
|---|---|---|
| 単一の開発用データベース | ✓ | |
| 小規模テスト環境 | ✓ | |
| 複数のSQL Serverインスタンス | ✓ | |
| エンタープライズ本番環境 | ✓ | |
| コンプライアンス要件の対応 | ✓ | |
| バックアップの一元監視 | ✓ | |
| 複数種類の業務基盤保護 | ✓ | |
| 復旧作業の自動管理 | ✓ |
多くの企業ではT-SQLはバックアップ作成に有用なツールとして活用し、統合プラットフォームは大規模環境での管理・復旧作業の簡素化に役立てています。
クエリを使用してSQL Serverデータベースをバックアップする方法は？
BACKUP DATABASEコマンドを使用します：
BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak';上記コマンドにより、指定したデータベースの完全バックアップが作成されます。
データベース完全バックアップ用のSQLクエリは？
一般的な例：
BACKUP DATABASE SalesDB
TO DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH COMPRESSION;これにより圧縮済みの完全バックアップファイルが生成されます。
バックアップファイルからSQL Serverデータベースを復元する方法は？
以下を実行します：
RESTORE DATABASE SalesDB
FROM DISK = 'D:\SQLBackups\SalesDB_Full.bak'
WITH REPLACE;指定したバックアップファイルからデータベースが復元されます。
SQL Serverバックアップクエリを自動化できますか？
可能です。SQL Server Agentを使用すると、事前に定義した間隔で自動実行するバックアップジョブをスケジュール設定できます。
完全バックアップと差分バックアップの違いは？
完全バックアップにはデータベース全体のデータが含まれます。
差分バックアップには、直前の完全バックアップ以降に変更されたデータのみが記録されます。
SQL Serverバックアップファイルを検証する方法は？
以下を使用します：
RESTORE VERIFYONLY
FROM DISK = 'D:\SQLBackups\SalesDB_Full.bak';データベースを復元せずにバックアップファイルの正当性を検証できます。
バックアップにはSSMSよりSQLクエリの方が優れていますか？
どちらの手法でも同一のバックアップファイルが作成されます。ただし、自動化、スクリプト作成、大規模データベース運用管理にはT-SQLクエリが一般的に推奨されます。
クエリによるSQL Serverデータベースバックアップの実行方法を習得することは、DBAやIT担当者に必須のスキルです。T-SQLによるバックアップ操作を習熟することで、完全バックアップ、差分バックアップ、トランザクションログバックアップの作成、バックアップ完全性検証、自動スケジュール設定、障害時のデータ復元を実施できます。
しかし、バックアップの作成は完全なデータ保護戦略の一部に過ぎません。企業はバックアップの正常実行監視、復元可否検証、保存期間ポリシー管理、障害時の復旧時間短縮も実施する必要があります。
小規模環境ではSQL Server標準のバックアップクエリで十分です。大規模・複雑なインフラ環境では、i2Backupのようなソリューションを活用することでバックアップ管理を簡素化し、運用効率を向上させ、全体のデータ保護体制を強化できます。