TSQL #66 – Monitoring Backup Lösung – Backup Historie

Aus mehrfachem Anlass möchte auch ich etwas zum Thema Backup-Historie schreiben, erst einmal weil Catherine Wilhelmsen den TSQL Tuesday #66 zum Thema Monitoring gehostet hat und weil Thomas Larock gerade heute eine ähnliche Lösung vorgestellt hat.

Wir DBAs kennen das leidige Thema Backup-Überwachung… lief es oder lief es nicht, kann ich meinen SLA dem Kunden gegenüber noch halten?
Wir haben bei einer Vielzahl von SQL Servern vertraglich zugesagt täglich das Monitoring zu prüfen, es muss mindestens ein Full-Backup innerhalb der letzten 24 Stunden erstellt worden sein. Ist dies nicht der Fall wird automatisiert – aus dem Reporting Tool heraus – ein Ticket „Bitte Backup überprüfen“ erstellt.

Daher hat mein Statement auch diese Einschränkungen, welche sich natürlich individuell je nach Bedürfnissen anpassen lassen.
Idealerweise schreibt man das Ergebnis der Abfrage in eine Tabelle um historische Daten über das Backup zu sammeln, so kann man aus den jeweiligen Backup-Größen einen Trend ermitteln.

/*
Get all Backup History Data - used at Customer XYZ for Reporting 
*/

SELECT 
  s.name as [Database], 
  (select bmf.physical_device_name from msdb..backupmediafamily bmf where bmf.media_set_id = bs.media_set_id) as [Backup_Path_File],
  bs.backup_start_date as DATE_BEGIN, 
  bs.backup_finish_date as DATE_END, 
  CASE WHEN bs.backup_start_date > DATEADD(dd,-7,getdate())
    THEN 0
    ELSE 1
  END as [Status],
  CASE WHEN bs.backup_start_date > DATEADD(dd,-1,getdate())
    THEN 'Backup is current within a day'
     WHEN bs.backup_start_date > DATEADD(dd,-7,getdate())
    THEN 'Backup is current within a week'
    ELSE '*****CHECK BACKUP!!!*****'
  END as [Message],
  convert(varchar,cast(bs.backup_size/1024/1024 as money),10) as [Backup_Volume],
  GETDATE() as [Date_Checked]
from 	master..sysdatabases s LEFT OUTER JOIN	msdb..backupset bs ON s.name = bs.database_name
  AND bs.backup_start_date = (SELECT MAX(backup_start_date) FROM msdb..backupset WHERE database_name = bs.database_name AND type = 'D')
WHERE	s.name <> 'tempdb'
ORDER BY s.name
GO

Mit dem Ergebnis der Abfrage kann man nun viele Dinge machen, zum Beispiel anhand der Spalte „Status“ die jeweilige Zeile farbig markieren, um die fehlerhaften Sicherungen besser kenntlich zu machen. Oder man passt die Betreffzeile einer Status-Mail entsprechend an.
Wir haben eine Kombination der SQL Server Alerts mit obigem Abfrage-Skript, welches uns dann darüber informiert, wo wir nachzubessern haben.

Zusätzlich empfehle ich die Einrichtung des SQL Alerts „SQL Server Alert System: ‚Severity 016‘ occurred on SERVER_XYZ“ mit Mail-Alarmierung (hierzu siehe auch diesen Blogbeitrag zu SQL Server Alerts).

Nachtrag:
Ich bin in der letzten Woche über ein Storage-Problem gestolpert, wo dieses Skript auch nicht wirklich sauber funktionierte, da das Backup zwar lief aber leider sehr sehr langsam, so dass es nicht innerhalb von 40 Minuten durch war (wie gewohnt) sondern leider rund 24 Stunden lief… dadurch war die Analyse mittels Skript nicht immer zu 100% aussagekräftig, aber hier wusste man ja um das Problem… 😉