SQL Server backup historyのExcel表示では、画面に値が出たことと目的を満たしたことを分けて考えます。結論は「SQL Serverではmsdbのbackupset等からdatabase、type、開始/終了、size、media pathをread-only取得できます。latest rowがあるだけでbackup成功・復元可能とはせず、copy-only、full/diff/log chain、retention、restore testをDBAが確認します。」。DB製品をSQL Serverと明示し、backup file存在・restore可否とhistory rowを同一視しない条件のもと、出力の意味、境界値、影響のある操作を順に確認します。 確認ポイント：msdbの履歴行はbackup実行記録であり、媒体が読めることやrestore成功を単独では証明しません。
msdb backup履歴で確認できる範囲を定義する
SQL Serverのバックアップ履歴を表示する処理では、msdbを参照できる読み取り権限と対象データベース名を確認します。backupsetの終了日時、種類、サイズ、保存先を最大100件など明示した上限で取得し、日時のタイムゾーンを注記します。履歴0件と接続エラーを分け、バックアップの実行や削除は行いません。
- 対象DB製品とinstance、timezoneを確認する
- msdb閲覧だけの最小権限を用意する
- backup type D/I/Lとcopy_onlyを理解する
- 結果の保存先とserver/media pathの機密性を確認する
backupsetとmediafamilyをread-only queryする
取得列とTOP件数をSQLで固定する
sql = "SELECT TOP (100) bs.database_name, bs.type, bs.backup_start_date, bs.backup_finish_date, bs.backup_size, bs.compressed_backup_size, bs.is_copy_only, bmf.physical_device_name FROM msdb.dbo.backupset AS bs LEFT JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id=bmf.media_set_id ORDER BY bs.backup_finish_date DESC"実行はread-only accountで行い、SELECT *を使いません。
ADO CommandからRecordsetを取得する
Set cmd=CreateObject("ADODB.Command")
Set cmd.ActiveConnection=cn
cmd.CommandText=sql
Set rs=cmd.Executeconnection stringへpasswordを直書きしません。
列名を保持してBackupReportへ出力する
For i=0 To rs.Fields.Count-1
ws.Cells(1,i+1).Value2=rs.Fields(i).Name
Next i
ws.Range("A2").CopyFromRecordset rs新規report sheetへ出力します。
D・I・Lを表示名へ変換する
Select Case ws.Cells(r,typeColumn).Value2
Case "D": label="Database"
Case "I": label="Differential"
Case "L": label="Log"
Case Else: label="Other"
End Select未知typeを成功扱いしません。
databaseごとのRPO閾値で鮮度を読む
' backup_finish_dateをserver timezone込みで現在時刻と比較し、DBごとのRPO閾値を適用する
' 固定24時間を全DBへ使わないDBA定義のRPO/RTOを使います。
履歴行と実媒体の復元可能性を区別する
backupset rowはhistoryであり、media fileが現存し読めることやrestore成功を保証しません。一つのbackup setが複数media familyへspanする場合があります。full、differential、log chainとcopy-onlyを区別し、AG/managed service等では別のbackup仕組みもあります。
定期確認はdatabase単位の最新完了時刻で行う
SQL Server Management Studioのbackup history、storage上media、DBAのrestore test結果を照合します。DB別RPO閾値、type、copy-only、finish time、sizeを確認し、0件・権限不足・connection failureを別statusへします。
device pathの機密性と権限不足を扱う
- 架空YourBackupInfoTableを前提にする
- 最新rowを復元可能と断定する
- backup typeを見ない
- media pathを公開する
- Excelからrestoreを自動実行する
復旧試験なしでbackup成功と断定しない
msdbへSELECTだけ行い、backup、delete history、restoreをExcel VBAから実行しません。physical_device_nameやDB名は内部情報なのでreportを限定共有します。credentialはtrusted connectionまたはsecret管理を使います。macro失敗時はconnectionを閉じ、report sheetだけを破棄します。backup本体の変更や移動は行いません。
backup履歴取得の合格基準
msdb.dbo.backupsetとbackupmediafamilyをread-only ADO queryし、database、type、start/finish、size、copy-onlyをBackupReportへ出します。connection stringと出力sheetを同じSubで定義し、D/I/Lの表示名と取得時刻を追加します。
物理pathを制御して出力する完成コード
Option Explicit
Public Sub ExportBackupHistory(ByVal connectionString As String, _
Optional ByVal includeDevicePath As Boolean = False)
Const adModeRead As Long = 1, adCmdText As Long = 1
Dim cn As Object, cmd As Object, rs As Object, ws As Worksheet
Dim sql As String, i As Long, outRow As Long, value As Variant, typeLabel As String
On Error GoTo CleanFail
sql = "SELECT TOP (100) bs.database_name, bs.type, bs.backup_start_date, " & _
"bs.backup_finish_date, bs.backup_size, bs.compressed_backup_size, " & _
"bs.is_copy_only, bmf.physical_device_name " & _
"FROM msdb.dbo.backupset AS bs " & _
"LEFT JOIN msdb.dbo.backupmediafamily AS bmf ON bs.media_set_id=bmf.media_set_id " & _
"ORDER BY bs.backup_finish_date DESC"
Set cn = CreateObject("ADODB.Connection")
cn.Mode = adModeRead
cn.CommandTimeout = 30
cn.Open connectionString
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = sql
Set rs = cmd.Execute
Set ws = ThisWorkbook.Worksheets("BackupReport")
ws.Cells.ClearContents
For i = 0 To rs.Fields.Count - 1
ws.Cells(1, i + 1).Value2 = rs.Fields(i).Name
Next i
ws.Cells(1, rs.Fields.Count + 1).Value2 = "type_label"
ws.Cells(1, rs.Fields.Count + 2).Value2 = "retrieved_at"
outRow = 2
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
value = rs.Fields(i).Value
If rs.Fields(i).Name = "physical_device_name" And Not includeDevicePath Then value = "[restricted]"
If IsNull(value) Then value = Empty
ws.Cells(outRow, i + 1).Value = value
Next i
Select Case CStr(rs.Fields("type").Value)
Case "D": typeLabel = "Database"
Case "I": typeLabel = "Differential"
Case "L": typeLabel = "Log"
Case Else: typeLabel = "Other"
End Select
ws.Cells(outRow, rs.Fields.Count + 1).Value2 = typeLabel
ws.Cells(outRow, rs.Fields.Count + 2).Value2 = Now
outRow = outRow + 1
rs.MoveNext
Loop
Debug.Print "backupRows=" & outRow - 2, "devicePathIncluded=" & includeDevicePath
CleanExit:
On Error Resume Next
If Not rs Is Nothing Then If rs.State <> 0 Then rs.Close
If Not cn Is Nothing Then If cn.State <> 0 Then cn.Close
Set rs = Nothing: Set cmd = Nothing: Set cn = Nothing
Exit Sub
CleanFail:
Dim e As Object
Debug.Print Err.Number, Err.Description
If Not cn Is Nothing Then For Each e In cn.Errors: Debug.Print e.Number, e.Description: Next e
Resume CleanExit
End Subこのcodeは画面表示だけを見るための例ではありません。通常系では「最大100件をfinish date降順で取得し、typeをDatabase/Differential/Logへ変換して出力する」を確認し、0件と実行errorを別の結果として保存します。
履歴あり・0件・msdb権限errorを分類する
- 期待どおり：最大100件をfinish date降順で取得し、typeをDatabase/Differential/Logへ変換して出力する
- 0件・非適用：権限内で履歴0件ならNoHistoryとしてheaderだけを残す
- 実行error：msdb permission、provider、connection、query、sheet write errorはErr/ADO Errorsとして停止する
backup履歴はrestore成功の証明ではありません。physical_device_nameは内部pathを含むため共有版ではredactします。固定24時間で全DBを判定せず、server timezoneとDB別RPOを別設定で評価します。
未知type・Null size・同一media複数行を試す
' D/I/L/未知type、0件、msdb権限不足をtestし、
' TypeLabel、Rows、RetrievedAt、error stateが期待どおりか確認する。full/diff/log/copy-only、0件、複数media family、permission errorをtestします。server名のredacted値、timezone、取得件数、最新finish、DB別RPO、restore test参照を保存します。
Excel VBAでデータベースのバックアップ情報を取得・表示する方法の証跡には、実行対象と取得時刻に加え、通常・0件・errorのどれへ分類したかを残します。通常系は「最大100件をfinish date降順で取得し、typeをDatabase/Differential/Logへ変換して出力する」、停止系は「msdb permission、provider、connection、query、sheet write errorはErr/ADO Errorsとして停止する」を判断文としてそのまま作業票へ写し、担当者ごとの言い換えで意味が変わらないようにします。
backup履歴reportを定期生成する場合は、SQL Server側の時刻とreport時刻のtimezoneを揃え、databaseごとに最後のfull・differential・log backupを別列へ配置します。copy-onlyを通常chainへ混ぜず、media familyが複数なら一つのfile pathへ縮約しません。履歴の存在をrestore可能性の証明とはせず、承認済みrestore testの参照がないdatabaseをRPO判定とは別に警告します。
コメント