Peter Larsson Blog

Patron Saint of Lost Yaks

How to tell who did a backup when

SELECT      db.name AS DatabaseName,
            bf.logical_name AS LogicalName,
            CASE bs.[type]
                        WHEN 'D' THEN 'Database'
                        WHEN 'I' THEN 'Differential database'
                        WHEN 'L' THEN 'Log'
                        WHEN 'F' THEN 'File or filegroup'
                        WHEN 'G' THEN 'Differential file'
                        WHEN 'P' THEN 'Partial'
                        WHEN 'Q' THEN 'Differential partial'
                        ELSE 'Unknown'
            END AS BackupType,
            CASE bf.file_type
                        WHEN 'D' THEN 'SQL Server data file'
                        WHEN 'L' THEN 'SQL Server log file'
                        WHEN 'F' THEN 'Full text catalog'
                        ELSE 'Unknown'
            END AS FileType,
            bs.user_name AS UserName,
            bs.backup_start_date AS StartDate,
            bs.backup_finish_date AS FinishDate,
            CAST(bs.software_major_version AS VARCHAR(11)) + '.'
                        + CAST(bs.software_minor_version AS VARCHAR(11)) + '.'
                        + CAST(bs.software_build_version AS VARCHAR(11)) AS ServerVersion,
            bs.[compatibility_level] AS CompatibilityLevel,
            bs.backup_size AS BackupSize
FROM        msdb..backupfile AS bf
INNER JOIN msdb..backupset AS bs ON bs.backup_set_id = bf.backup_set_id
INNER JOIN master..sysaltfiles AS af ON af.name = bf.logical_Name
INNER JOIN master..sysdatabases AS db ON db.dbid = af.dbid
ORDER BY    db.name,
            bf.logical_name,
            bs.backup_finish_date DESC

Legacy Comments


Remote DBA
2009-02-12
re: How to tell who did a backup when
Thanks :). I always mess up with backupset, backupmediafamily and other msdb tables and try to encrypt this info on my own. Now I will use your script!
That would be also to return information about backup type - full diff or log...

Kekerode
2009-02-24
re: How to tell who did a backup when
Great problem shooting script :)

Thank u very much