Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

Print | posted on Thursday, February 12, 2009 10:36 AM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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...
2/12/2009 5:04 PM | Remote DBA
Gravatar

# re: How to tell who did a backup when

Great problem shooting script :)

Thank u very much
2/24/2009 6:37 PM | Kekerode
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET