# 機能設計書 8-sp_BlitzBackups

## 概要

本ドキュメントは、SQL Server First Responder Kitのsp_BlitzBackupsストアドプロシージャの機能設計について記載する。sp_BlitzBackupsはバックアップ履歴を分析し、RPO（Recovery Point Objective）とRTO（Recovery Time Objective）の観点からリスクを評価する診断ツールである。

### 本機能の処理概要

sp_BlitzBackupsは、msdbデータベースのバックアップ履歴を分析し、データ損失リスク（RPO）とリカバリ所要時間（RTO）を算出するストアドプロシージャである。

**業務上の目的・背景**：バックアップは取得しているが、実際に障害が発生した場合にどれだけのデータを失うか、復旧にどれだけ時間がかかるかを把握していないケースが多い。sp_BlitzBackupsはバックアップ履歴から最悪ケースのRPO/RTOを算出し、潜在的なリスクを可視化する。

**機能の利用シーン**：
- バックアップ戦略の妥当性評価
- 災害復旧計画（DRP）の策定支援
- バックアップ間隔の最適化検討
- リストア時間の見積もり

**主要な処理内容**：
1. バックアップ履歴の収集と分析
2. RPO最悪ケース（バックアップ間の最大ギャップ）の算出
3. RTO最悪ケース（リストアに必要な時間）の算出
4. バックアップスループットの統計
5. 警告の生成と出力

**関連システム・外部連携**：
- msdb.dbo.backupset
- msdb.dbo.backupmediafamily
- Availability Group環境でのリスナー連携

**権限による制御**：
- msdbデータベースへの読み取り権限が必要

## 関連画面

| 画面No | 画面名 | 関連種別 | 関連する操作・処理 |
|--------|--------|----------|------------------|
| 11 | sp_BlitzBackups結果画面 | 主機能 | バックアップ履歴からRPO/RTOを分析・表示 |

## 機能種別

データ分析 / バックアップ診断 / リスク評価

## 入力仕様

### 入力パラメータ

| パラメータ名 | 型 | 必須 | 説明 | バリデーション |
|-------------|-----|-----|------|---------------|
| @Help | TINYINT | No | ヘルプ表示 | デフォルト0 |
| @HoursBack | INT | No | 分析対象期間（時間） | デフォルト168（7日間） |
| @MSDBName | NVARCHAR(256) | No | MSDBデータベース名 | デフォルト'msdb' |
| @AGName | NVARCHAR(256) | No | Availability Group名 | NULL許可 |
| @RestoreSpeedFullMBps | INT | No | フルリストア速度（MB/秒） | NULL時はバックアップ速度を使用 |
| @RestoreSpeedDiffMBps | INT | No | 差分リストア速度（MB/秒） | NULL時はバックアップ速度を使用 |
| @RestoreSpeedLogMBps | INT | No | ログリストア速度（MB/秒） | NULL時はバックアップ速度を使用 |
| @Debug | TINYINT | No | デバッグモード | デフォルト0 |
| @PushBackupHistoryToListener | BIT | No | リスナーへ履歴プッシュ | デフォルト0 |
| @WriteBackupsToListenerName | NVARCHAR(256) | No | 書き込み先リスナー名 | NULL許可 |
| @WriteBackupsToDatabaseName | NVARCHAR(256) | No | 書き込み先DB名 | NULL許可 |
| @WriteBackupsLastHours | INT | No | 書き込み対象期間 | デフォルト168 |
| @Version | VARCHAR(30) | No | バージョン（OUTPUT） | 出力専用 |
| @VersionDate | DATETIME | No | バージョン日付（OUTPUT） | 出力専用 |
| @VersionCheckMode | BIT | No | バージョンチェックモード | デフォルト0 |

### 入力データソース

- msdb.dbo.backupset: バックアップセット情報
- msdb.dbo.backupmediafamily: バックアップメディア情報
- sys.databases: データベース情報

## 出力仕様

### 出力データ

**メインサマリー**:
| 項目名 | 型 | 説明 |
|--------|-----|------|
| database_name | NVARCHAR(128) | データベース名 |
| database_guid | UNIQUEIDENTIFIER | データベースGUID |
| RPOWorstCaseMinutes | DECIMAL(18,1) | RPO最悪ケース（分） |
| RTOWorstCaseMinutes | DECIMAL(18,1) | RTO最悪ケース（分） |
| FullMBpsAvg | DECIMAL(18,2) | フルバックアップ平均速度 |
| FullSizeMBAvg | DECIMAL(18,2) | フルバックアップ平均サイズ |
| DiffMBpsAvg | DECIMAL(18,2) | 差分バックアップ平均速度 |
| LogMBpsAvg | DECIMAL(18,2) | ログバックアップ平均速度 |

**RTOリカバリポイント**:
| 項目名 | 型 | 説明 |
|--------|-----|------|
| rto_worst_case_size_mb | DECIMAL | リストア対象合計サイズ |
| rto_worst_case_time_seconds | INT | 推定リストア時間（秒） |
| full_backup_set_id | INT | フルバックアップセットID |
| diff_backup_set_id | INT | 差分バックアップセットID |
| log_backups | INT | ログバックアップ数 |

**警告一覧**:
| 項目名 | 型 | 説明 |
|--------|-----|------|
| CheckId | INT | チェックID |
| Priority | INT | 優先度 |
| DatabaseName | NVARCHAR(128) | データベース名 |
| Finding | VARCHAR(256) | 発見事項 |
| Warning | VARCHAR(8000) | 警告メッセージ |

### 出力先

- 画面表示（複数の結果セット）

## 処理フロー

### 処理シーケンス

```
1. パラメータ検証と初期化
   └─ @HoursBackを負の値に変換

2. バックアップ統計収集
   ├─ フル/差分/ログ別のMBps算出
   └─ 圧縮サイズ統計の収集

3. RPO最悪ケース計算
   ├─ バックアップ間ギャップの検出
   └─ 最大ギャップの特定

4. RTOリカバリポイント計算
   ├─ フルバックアップの特定
   ├─ 差分バックアップの特定
   └─ ログバックアップチェーンの計算

5. RTO最悪ケース計算
   └─ リストア時間の推定

6. リカバリ可能性分析
   └─ バックアップ成長傾向の分析

7. 警告生成
   └─ 潜在的問題の特定

8. 結果出力
   └─ 複数結果セットの返却
```

### フローチャート

```mermaid
flowchart TD
    A[開始] --> B{@Help = 1?}
    B -->|Yes| C[ヘルプ出力]
    C --> Z[終了]
    B -->|No| D{@MSDBName存在確認}
    D -->|No| E[エラー終了]
    D -->|Yes| F{@PushBackupHistoryToListener?}
    F -->|Yes| G[リスナーへプッシュ]
    G --> Z
    F -->|No| H[#Backups作成]
    H --> I[バックアップ統計収集]
    I --> J[RPO最悪ケース計算]
    J --> K[#RTORecoveryPoints作成]
    K --> L[フル/差分/ログ分析]
    L --> M[RTO最悪ケース計算]
    M --> N[#Recoverability分析]
    N --> O[#Trending分析]
    O --> P[#Warnings生成]
    P --> Q[結果出力]
    Q --> Z
```

## ビジネスルール

### 業務ルール

| ルールNo | ルール名 | 内容 | 適用条件 |
|---------|---------|------|---------|
| BR-001 | デフォルト期間 | @HoursBack未指定時は168時間（7日間） | @HoursBack=NULL |
| BR-002 | 負値変換 | @HoursBackが正の場合は負に変換 | @HoursBack > 0 |
| BR-003 | リストア速度 | @RestoreSpeed未指定時はバックアップ速度を使用 | @RestoreSpeed*=NULL |
| BR-004 | ゼロ除算回避 | バックアップ時間0秒の場合は1秒として計算 | DATEDIFF=0 |
| BR-005 | 差分サイズ警告 | フル10GB超かつ差分がフルの40%超で警告 | IsBigDiff計算 |
| BR-006 | ログサイズ警告 | フル10GB超かつログがフルの20%超で警告 | IsBigLog計算 |

### 計算ロジック

**バックアップスループット計算**:
```sql
MBpsAvg = AVG(backup_size / (CASE WHEN DATEDIFF(ss, backup_start_date, backup_finish_date) = 0
  THEN 1 ELSE DATEDIFF(ss, backup_start_date, backup_finish_date) END) / 1048576)
```

**RPO最悪ケース計算**:
```sql
RPOWorstCaseMinutes = max_backup_gap_seconds / 60.0
```

**RTO最悪ケース計算**:
```sql
RTOWorstCaseMinutes = (full_time / 60) + (diff_time / 60) + (log_time / 60)
-- @RestoreSpeed指定時は速度ベースで計算
```

**差分サイズ判定**:
```sql
IsBigDiff = CASE WHEN (AvgFullSizeMB > 10240 AND ((AvgDiffSizeMB * 100) / AvgFullSizeMB >= 40)) THEN 1 ELSE 0 END
```

## データベース操作仕様

### 操作別データベース影響一覧

| 操作 | 対象テーブル | 操作種別 | 概要 |
|-----|-------------|---------|------|
| 参照 | msdb.dbo.backupset | SELECT | バックアップ履歴の取得 |
| 参照 | msdb.dbo.backupmediafamily | SELECT | バックアップメディア情報 |
| 参照 | sys.databases | SELECT | データベース存在確認 |
| 挿入 | リスナーDB | INSERT | 履歴プッシュ時（オプション） |

### テーブル別操作詳細

#### #Backups（一時テーブル）

| 操作 | 項目（カラム名） | 更新値・取得条件 | 備考 |
|-----|-----------------|-----------------|------|
| INSERT | database_name | backupsetから取得 | DB名 |
| INSERT | FullMBpsAvg/Min/Max | 速度統計 | フルバックアップ速度 |
| INSERT | DiffMBpsAvg/Min/Max | 速度統計 | 差分バックアップ速度 |
| INSERT | LogMBpsAvg/Min/Max | 速度統計 | ログバックアップ速度 |
| UPDATE | RPOWorstCaseMinutes | 最大ギャップから計算 | RPO更新 |
| UPDATE | RTOWorstCaseMinutes | リストア時間から計算 | RTO更新 |

#### #RTORecoveryPoints（一時テーブル）

| 操作 | 項目（カラム名） | 更新値・取得条件 | 備考 |
|-----|-----------------|-----------------|------|
| INSERT | full_backup_set_id | フルバックアップID | リカバリポイント |
| INSERT | diff_backup_set_id | 差分バックアップID | リカバリポイント |
| INSERT | log_last_lsn | 最終ログLSN | ログチェーン終端 |
| UPDATE | rto_worst_case_size_mb | 計算列 | 合計サイズ |

## エラー処理

### エラーケース一覧

| エラーコード | エラー種別 | 発生条件 | 対処方法 |
|------------|----------|---------|---------|
| - | DB不在 | @MSDBNameが存在しない | RAISERRORでエラー通知、RETURN |

### リトライ仕様

特定のリトライ機能は実装されていない。

## トランザクション仕様

READ UNCOMMITTED分離レベルで実行される。

## パフォーマンス要件

- @HoursBackで分析期間を制限可能
- 大規模環境ではバックアップ履歴が膨大になる可能性あり

## セキュリティ考慮事項

- msdbデータベースへの読み取り権限が必要
- 動的SQLを使用するため、入力値はQUOTENAME()で保護

## 備考

- バージョン: 8.29（2026年2月3日時点）
- SQL Server 2008以降対応（2005/2000は非対応）
- WITH RECOMPILEオプションで実行

---

## コードリーディングガイド

本機能を理解するために参照すべきファイルと、推奨する読み解き順序を以下に示す。

### 推奨読解順序

#### Step 1: データ構造を理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 1-1 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **124-165行目**: #Backups一時テーブル定義 |
| 1-2 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **167-190行目**: #RTORecoveryPoints一時テーブル定義 |
| 1-3 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **192-213行目**: #Recoverability一時テーブル定義 |
| 1-4 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **215-232行目**: #Trending一時テーブル定義 |
| 1-5 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **235-243行目**: #Warnings一時テーブル定義 |

**読解のコツ**:
- #Backups: RPO/RTO分析の主要結果を格納
- #RTORecoveryPoints: リカバリポイントのLSNチェーンを追跡
- #Recoverability: バックアップの復旧可能性を評価
- rto_worst_case_size_mbとrto_worst_case_time_secondsは計算列

#### Step 2: エントリーポイントを理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 2-1 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **4-20行目**: ALTER PROCEDURE定義とパラメータ |
| 2-2 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **22-32行目**: 初期設定とバージョンチェック |

**主要処理フロー**:
1. **23-25行目**: SET NOCOUNT ON、STATISTICS XML OFF、READ UNCOMMITTED
2. **27行目**: バージョン情報設定（8.29）
3. **34-97行目**: @Help=1時のヘルプ出力

#### Step 3: バックアップ統計収集を理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 3-1 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **255-315行目**: バックアップ統計INSERT |

**主要処理フロー**:
- **259-273行目**: CTE「Backups」でフル/差分/ログ別統計を計算
- **275-310行目**: フル(D)/差分(I)/ログ(L)をJOINして#Backupsに挿入

#### Step 4: RPO最悪ケース計算を理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 4-1 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **318-361行目**: RPO計算 |

**主要処理フロー**:
- **322-339行目**: #backup_gaps一時テーブルでバックアップ間ギャップを計算
- **341-356行目**: max_gaps CTEで最大ギャップを特定
- **347-356行目**: #Backupsを最大ギャップ情報で更新

#### Step 5: RTOリカバリポイント計算を理解する

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 5-1 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **376-396行目**: 最新ログLSNの収集 |
| 5-2 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **399-431行目**: フルバックアップの特定 |
| 5-3 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **478-499行目**: 差分バックアップの特定 |
| 5-4 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **525-553行目**: ログバックアップ時間の計算 |
| 5-5 | sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | **555-596行目**: RTO最悪ケースの計算 |

### プログラム呼び出し階層図

```
sp_BlitzBackups
    │
    ├─ パラメータ検証
    │   └─ sys.databases でMSDB存在確認
    │
    ├─ #Backups一時テーブル
    │   └─ msdb.dbo.backupset から統計収集
    │
    ├─ RPO分析
    │   ├─ #backup_gaps でギャップ検出
    │   └─ max_gaps CTE で最大値特定
    │
    ├─ #RTORecoveryPoints一時テーブル
    │   ├─ ログLSN収集
    │   ├─ フルバックアップLSN
    │   ├─ 差分バックアップLSN
    │   └─ リストア時間計算
    │
    ├─ #Recoverability一時テーブル
    │   └─ バックアップサイズ傾向分析
    │
    ├─ #Trending一時テーブル
    │   └─ 12ヶ月分のサイズ傾向
    │
    └─ #Warnings一時テーブル
        └─ 警告メッセージ生成
```

### データフロー図

```
[入力]                           [処理]                              [出力]

パラメータ ──────────────────▶ パラメータ検証 ─────────────────▶
   │                              │
   │                              ▼
msdb.dbo.backupset ──────────▶ 統計計算 ───────────────────────▶ #Backups
   │                              │                                   │
   │                              ▼                                   │
   │                        バックアップギャップ ──────────────▶ RPOWorstCase
   │                              │
   │                              ▼
   │                        #RTORecoveryPoints
   │                              │
   │                              ├─ フルバックアップLSN
   │                              ├─ 差分バックアップLSN
   │                              └─ ログバックアップLSN
   │                              │
   │                              ▼
   │                        RTO計算 ────────────────────────────▶ RTOWorstCase
   │                              │
   │                              ▼
   │                        #Recoverability ───────────────────▶ 傾向分析
   │                              │
   │                              ▼
   │                        #Warnings ─────────────────────────▶ 警告一覧
   │                              │
   ▼                              ▼
結果出力 ◀───────────────────────────────────────────────────────────┘
```

### 関連ファイル一覧

| ファイル | パス | 種別 | 役割 |
|---------|------|------|------|
| sp_BlitzBackups.sql | `sp_BlitzBackups.sql` | ソース | メインストアドプロシージャ定義 |
