Använd backuphistoriken i msdb!

Varje gång det tas en backup i SQL Server, oavsett hur backupen tas, loggas information om den i systemdatabasen msdb. Den används bland annat när man vill göra en återläsning av en tidigare version av en databas (restore) i SQL Server Management Studio (SSMS). När man då väljer vilken databas som skall återläsas hämtar SSMS senast gjorda backup, inklusive eventuella senare backuper av transaktionsloggen. Det är mycket information om backuperna som sparas och även om en hel del är tekniskt och sådant man sällan behöver dagligdags kan vi ha stor nytta av den. Jag brukar använda mig av ett skript som bara listar informationen, oftast i fallande tidsordning (se nedan).   Låt oss titta på några exempel.

Lista backuper med valfritt filter

Bara en enkel lista kan ge oss svar på många frågor. Vi kan enkelt filtrera valfri databas, tidsperiod etc. Jag brukar normalt ha skriptet med sortering i fallande tidsordning eftersom man oftast vill se det mest aktuella.

Har vi alla backuper av transaktionsloggen på samma plats?

Då och då när vi besöker kunder upptäcker vi att de har flera backupsystem som tar backup av transaktionsloggen. T.ex. både till disk av SQL Servers maintenance plans och av deras centrala backupsystem. Bra med dubbel säkerhet? Nej – för att återläsa backuper av transaktionsloggen måste göras i obruten ordning och nu ligger spridda på två ställen och ska återläsas av två system! I listan ovan finns både plats och MediaTyp. Backuper tagna av centrala system har mediatyp Virtuell och en GUID som filnamn, så man brukar se det ganska snabbt.

Hur mycket disk behöver vi på sikt?

Eftersom SQL Servers backuper inte innehåller den ”luft” som bör finnas i databasfilerna utan bara dess data så ger backupstorlekens utveckling ett bra mått på hur mycket data vi samlar på oss. Hur länge historiken sparas beror på om det finns någon rensning uppsatt, via maintenance plan eller annat. Annars ligger den kvar, på gott och ont. Positivt för att se trender över tid, men å andra sidan ligger systemdatabaserna (inkl msdb) på C: och inte tillsammans med dina vanliga databaser. Har du många databaser/backuper kommer den att växa. Med data från skriptet nedan och Excel kan vi enkelt se utvecklingen över tid. I detta exempel ser vi att det inte alltid ökar i samma takt hel tiden.

Skript för att titta på backuphistorik

SELECT
   DatabasNamn = bs.database_name 
 , BackupDatum = convert(char(10),bs.backup_start_date,120)
 , BackupStart = CAST(bs.backup_start_date as TIME(0))
 , Minuter     = DATEDIFF(MINUTE, bs.backup_start_date, bs.backup_finish_date)
 , DbTyp       = CASE WHEN DB_ID(bs.database_name) <= 4 THEN 'System' ELSE 'User' END
 , BackupTyp   = CASE bs.type
                   WHEN 'D' THEN 'Database'
                   WHEN 'L' THEN 'Log'
                   WHEN 'F' THEN 'File(group)'
                   WHEN 'P' THEN 'Partial'
                   WHEN 'I' THEN 'Diff. database'
                   WHEN 'G' THEN 'Diff. file'
                   WHEN 'Q' THEN 'Diff. partial'
                   ELSE bs.type
                 END
 , MediaTyp    = CASE mf.device_type
                   WHEN 2 THEN 'Disk'
                   WHEN 5 THEN 'Tape'
                   WHEN 7 THEN 'Virtual'
                   WHEN 105 THEN 'Permanent'
                   ELSE CAST(mf.device_type AS VARCHAR(3))
                 END
 , CopyOnly    = bs.is_copy_only
 , StorlekMB   = CAST(bs.backup_size /SQUARE(1024) AS int)
 , DiskMB      = CAST(bs.compressed_backup_size /SQUARE(1024) AS int)
 , Fil         = mf.physical_device_name
FROM msdb.dbo.backupmediafamily mf 
INNER JOIN msdb.dbo.backupset bs ON mf.media_set_id = bs.media_set_id 
WHERE 1=1 
AND (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 7)
-- AND bs.type = 'D'
-- AND bs.database_name = 'ReportServer'
ORDER BY 
 bs.backup_finish_date desc