PowerShell で SQL Server のバックアップを自動化する — 完全 / 差分 / トランザクションログの3段構成

みなさんこんにちは！ヒロポンです！

客先に1人で常駐してると、ある朝いきなり「このDB、バックアップちゃんと取れてる？？」って聞かれること、ありませんか。

手動で SSMS から右クリック → バックアップ、を毎日やる？

無理です。忘れる。

俺も昔、手動運用にしてた現場で「先週ぶんが飛んだ」をやりかけて、血の気が引いたことがあるんですよね。

そこで PowerShell の出番。SQL Server には Backup-SqlDatabase というそのものズバリのコマンドがあって、これをタスクスケジューラに乗せれば、完全・差分・ログの3段バックアップが完全自動で回ります。

この記事は、環境準備から自動化の完走まで、業務SEが1人でも組めるように段階で追っていきます。コピペで動く .ps1 付きです。

完成イメージ

最終的に作るのは、この3段構成の自動バックアップです。

• 完全バックアップ — 毎週日曜の深夜に1回。DB全体のコピー
• 差分バックアップ — 毎日深夜。前回の完全からの変更ぶん
• トランザクションログバックアップ — 15分ごと。直近の更新を細かく拾う

なぜ3段に分けるか。復旧の細かさ（RPO）とバックアップの軽さを両立させるためです。

完全だけだと毎日DB全体をコピーして重いし、最後の完全以降は守れない。ログまで取ると、障害の15分前まで戻せる。

で、この3つは独立してるわけじゃなくて、復元するときにチェーンでつながってるのがポイントです。

復元は「完全を土台に、最新の差分を1つ、そのあとのログを順番に」当てていく。だから3つはセットで意味を持ちます。

前提条件・必要なもの

始める前に3つだけ確認します。

1. SqlServer PowerShell モジュール — Backup-SqlDatabase はこのモジュールに入っています（後の Step 1 で入れます）
2. 対象DBの復旧モデルが FULL — トランザクションログバックアップを取るには必須。SIMPLE だとログは取れません（理由は Step 3 で書きます）
3. バックアップ保存先フォルダ — ローカルでもネットワーク共有でも。今回は C:\SqlBackup を使います

復旧モデルは、まずこのT-SQLで FULL にしておきます。

-- 対象DBの復旧モデルを FULL にする (ログバックアップの前提)
ALTER DATABASE MainDB SET RECOVERY FULL;

Step 1: SqlServer モジュールを入れる

Backup-SqlDatabase を使うには SqlServer モジュールが要ります。管理者権限の PowerShell で1回入れればOK。

# SqlServer モジュールをインストール (初回のみ・管理者権限)
Install-Module -Name SqlServer -Scope AllUsers -Force

# 入ったか確認
Get-Command Backup-SqlDatabase

Get-Command で Backup-SqlDatabase が出てくれば準備完了です！！

Step 2: 完全バックアップを取る

まずは手で1回、完全バックアップを取ってみます。これが土台になります。

# 完全バックアップ (日付入りファイル名で保存)
$instance = "localhost"
$dbName   = "MainDB"
$backupDir = "C:\SqlBackup"
$stamp    = Get-Date -Format "yyyyMMdd_HHmmss"

Backup-SqlDatabase `
    -ServerInstance $instance `
    -Database       $dbName `
    -BackupFile     "$backupDir\${dbName}_full_$stamp.bak"

ポイントは1つ。Backup-SqlDatabase は 引数なしだと既定で完全バックアップになります。

-BackupFile で保存先とファイル名を指定。$stamp に日時を入れて、毎回別ファイルになるようにしてます。同名だと既定では追記されて、あとから分かりにくくなるんですよね。

これで C:\SqlBackup\ の下に MainDB_full_20260609_020000.bak みたいなファイルがこんな感じでできます。

Step 3: 差分とトランザクションログ

完全が取れたら、差分とログを足します。

ここでコマンドのオプションを間違えやすいので、正確にいきます。

# 差分バックアップ ... -Incremental スイッチを付ける
Backup-SqlDatabase `
    -ServerInstance $instance `
    -Database       $dbName `
    -BackupFile     "$backupDir\${dbName}_diff_$stamp.bak" `
    -Incremental

# トランザクションログバックアップ ... -BackupAction Log
Backup-SqlDatabase `
    -ServerInstance $instance `
    -Database       $dbName `
    -BackupFile     "$backupDir\${dbName}_log_$stamp.trn" `
    -BackupAction Log

え、差分なら -BackupAction Differential ちゃうの？？ってなりますよね。

でも違うんです。差分は -Incremental スイッチ。

-BackupAction が受け付ける値は Database / Files / Log の3つだけで、Differential という値そのものが存在しません。だから差分のつもりで -BackupAction Differential と書いても通らない。公式ドキュメントでも、差分は -Incremental（「差分バックアップを実行する」）と定義されています。

ログは -BackupAction Log で、拡張子は慣例で .trn にしておきます。

実行すると、完全・差分・ログの3つがそれぞれ別タイプで記録されます。

3種類の違いを整理すると、こんな感じになります。

この「ログは復旧モデルを選ぶ」という話、Pro SQL Server Internals（Dmitri Korotkevitch, p.668）のバックアップ章にズバリ書かれています。

完全バックアップと差分バックアップはすべての復旧モデルでサポートされるが、ログバックアップは FULL または BULK LOGGED 復旧モデルでのみサポートされる。差分バックアップは累積的で、最後の完全バックアップ以降に変更されたすべてのエクステントを含む。必要なときは最新の差分を復元できる。一方、ログバックアップは増分的で、以前のバックアップで取得済みのトランザクションログ部分は含まない。

俺の解釈はこう。差分は「累積」だから直近の1個だけ復元すればいい。ログは「増分」だから取った順に全部当てる必要がある。

そして SIMPLE 復旧モデルだとログバックアップ自体が取れない。だから前提条件で「FULL にしておく」が、ここで効いてくるわけです。

Step 4: タスクスケジューラで自動化する

手で取れることを確認したら、自動化します。各バックアップを .ps1 ファイルにして、Register-ScheduledTask でタスクスケジューラに登録します。

たとえば完全バックアップ用の FullBackup.ps1 を作って、それを毎週日曜2時に走らせる登録はこうです。

# 完全バックアップを毎週日曜 AM2時に自動実行する登録
$action  = New-ScheduledTaskAction -Execute "powershell.exe" `
    -Argument "-NoProfile -ExecutionPolicy Bypass -File C:\Scripts\FullBackup.ps1"
$trigger = New-ScheduledTaskTrigger -Weekly -DaysOfWeek Sunday -At "02:00"

Register-ScheduledTask `
    -TaskName "SqlServer_FullBackup" `
    -Action   $action `
    -Trigger  $trigger `
    -RunLevel Highest `
    -User     "SYSTEM"

これで3段がいい感じに揃います。差分は -Daily -At "02:00"、ログは15分ごと（New-ScheduledTaskTrigger -Once -At "00:00" -RepetitionInterval (New-TimeSpan -Minutes 15)）で同じように登録すれば、3段が自動で回り始めます。

最後に、古いバックアップの世代管理も入れておきます。

放っておくとディスクが .bak で埋まります。これ、地味にやられるやつ。

# 7日より古い .bak / .trn を削除する (各 .ps1 の末尾に入れておく)
Get-ChildItem -Path "C:\SqlBackup" -Include *.bak, *.trn -Recurse |
    Where-Object { $_.LastWriteTime -lt (Get-Date).AddDays(-7) } |
    Remove-Item -Force

Where-Object で「7日より前」を絞って Remove-Item。保持日数は運用に合わせて変えてください。

ひとつ注意。Get-ChildItem の -Include は -Recurse とセットじゃないと無視されます。-Recurse を外すと絞り込みが効かず、フォルダ内が丸ごと削除対象になりかねない。本番に流す前に、Remove-Item を -WhatIf 付きで一度走らせて「何が消えるか」だけプレビューしておくと安全です。

ちなみに、定例のチェーンとは別に臨時で1回だけバックアップを取りたいときは -CopyOnly を付けます。差分やログのチェーンに影響しないコピーが取れるので、検証用にDBを抜くときに重宝します。

動作確認

登録したら、まず手で1回流してみて、ファイルができるか確認します。

# 登録したタスクを手動で1回実行
Start-ScheduledTask -TaskName "SqlServer_FullBackup"

# 生成された .bak / .trn を確認
Get-ChildItem -Path "C:\SqlBackup" -Include *.bak, *.trn -Recurse |
    Select-Object Name, Length, LastWriteTime

.bak と .trn が日付つきでこんな感じに並んでいれば成功です。

本番で使う前に、別のテスト用DBへ Restore-SqlDatabase で復元できるかまで一度試しておくと安心です。バックアップは「取れてること」より「戻せること」が本番なので。

トラブルシューティング

実績ゼロから組むと、だいたいこの3つでつまずきます。

• Backup-SqlDatabase が見つからない → SqlServer モジュールが入ってません。Step 1 の Install-Module SqlServer を実行してください。
• BACKUP LOG は復旧モデル SIMPLE では許可されていません → 対象DBが SIMPLE 復旧モデルです。ALTER DATABASE MainDB SET RECOVERY FULL; で FULL に変えると、ログバックアップが取れるようになります。
• タスクは動くのにファイルができない → タスクの実行ユーザー（-User）にバックアップ先フォルダの書き込み権限がないことが多いです。SYSTEM か、SQL Server サービスアカウントに権限を付けてください。
• .ldf（ログファイル）がどんどん肥大していく → FULL 復旧モデルにしたのに、ログバックアップを回していないパターンです。FULL ではログバックアップを取るまでトランザクションログが切り詰められないので、.ldf が無限に膨らみます。FULL にしたら、ログバックアップもスケジュールに乗せる。これがワンセットです。1人運用でいちばん多い事故が、実はこれ。

SIMPLE 復旧モデルでログバックアップを取ろうとすると、実際こうエラーで弾かれます。

動作確認メモ: バックアップの中身（完全 / 差分 / ログが取れる・SIMPLE だとログが弾かれる・完全→差分→ログで復元できる）は SQL Server 2019 エンジンで実機検証済みです。一方、Install-Module SqlServer（Step 1）と Register-ScheduledTask 系（Step 4）は Windows + SqlServer モジュール環境が前提なので、お使いの本番に近い環境で一度動作確認してから組み込んでください。

まとめ

PowerShell での SQL Server バックアップ自動化、3段の組み方を整理します。

• 完全 → Backup-SqlDatabase -BackupFile X.bak（既定・無指定で完全）
• 差分 → -Incremental を付ける（-BackupAction Differential は存在しないので注意）
• ログ → -BackupAction Log。復旧モデルが FULL / BULK_LOGGED のときだけ取れる
• 自動化 → Register-ScheduledTask で日次完全 + 時間ごと差分 + 15分ログ
• 世代管理 → Get-ChildItem | Where LastWriteTime | Remove-Item で古い .bak を掃除

1人運用の現場でも、これを一度組んでおけば「バックアップ取れてる？」に即答できる。手動で毎日やるより、よっぽど安全です。

よくある質問

Q1. 差分バックアップは -BackupAction Differential じゃないの？

違います。-BackupAction が受け付けるのは Database / Files / Log の3つだけで、Differential はありません。差分バックアップは -Incremental スイッチで指定します。公式ドキュメントでも -Incremental が「差分バックアップを実行する」と定義されています。

Q2. SIMPLE 復旧モデルだとログバックアップが取れないのはなぜ？

SIMPLE ではトランザクションログがチェックポイントごとに自動で切り詰められ、ログを残しておく仕組みがないからです。ログバックアップは FULL か BULK_LOGGED 復旧モデルでのみ取れます。15分ごとのログで細かく戻したいなら、対象DBを FULL にしてください。

Q3. 完全と差分、どっちを先に戻すの？

完全が先で、その上に最新の差分を1つ当てます。差分は「累積」なので、最後の完全以降の差分は最新の1個だけ復元すればOKです。そのあとに、差分以降のログを取った順に当てていきます。

Q4. バックアップファイルが同じ名前で上書きされてしまう

-BackupFile に固定のファイル名を渡していると、既定では同じメディアセットに追記されていきます。ファイル名に Get-Date -Format "yyyyMMdd_HHmmss" で日時を入れて、毎回別ファイルにするのが運用しやすいです。古いものは世代管理（Step 4）で掃除します。

関連記事

• SQL Server UPDATE … FROM SELECT 3パターン — バックアップの前に、そもそものデータ更新を安全に書く話。JOIN / CTE / MERGE の使い分け
• SQL Server の一時テーブル・テーブル変数・CTE を使い分ける3つの判断軸 — 復元したデータを加工するときの退避先。tempdb 周りの基礎
• PowerShell の ErrorAction で見落とす3つの落とし穴 — 自動化スクリプトを止めない/止めるの制御。バックアップ失敗を握りつぶさないために
• PowerShell で AD ユーザーを CSV から一括作成する — 同じくタスクスケジューラに乗せる定番の自動化ネタ。1人運用の武器を増やす
• 「客先常駐しかない」業務SEが技術+αで抜ける道 — PowerShell で運用を自動化できる業務SEは、現場でどう評価が変わるか。手を動かす人のキャリア論

以上！

「手動バックアップでヒヤッとした」経験ある人いたら、どんどんシェア待ってるぜ！！

---

この記事の参考文献

ここまでの知見は以下の書籍から引用しています。業務SE視点で再構成していますが、元の体系的な知識を学ぶには直接読むのがおすすめです。

📖 『Pro SQL Server Internals』（著: Dmitri Korotkevitch）

引用範囲: p.668（第31章 バックアップとリストア・サマリ）
本の特徴: SQL Server の内部構造（ストレージ / インデックス / トランザクション / ロック / バックアップ・リストア / 復旧モデル）を DBA レベルで深掘りした技術書。完全 / 差分 / ログがなぜ復旧チェーンを組むのかを根本から理解できる。
こんな人におすすめ: 業務SE / DBA / SQL Server 案件担当・バックアップ運用を任されたエンジニア

---

執筆者

バイブス父さん — 業務SE 7年（SIer 正社員2 / フリーランス5）。現職は SEO 直轄部の AI アドバイザー兼 PL、副業で中小 SIer の CTO。SIer の正社員からフリーランスに転じ、複数のエージェント経由で案件を回してきた経験ベースで「業務SE視点」の技術 + キャリア記事を書いています。

🐦 X: @hiro_progra0524（日々の現場メモ更新中）
📝 About Me で経歴詳細を見る