SQL query for SQL Server backups

Question

I found that SQL query for MS SQL server 2008 to display backups.

SELECT      D.name
           ,ISNULL(CONVERT(VARCHAR,backup_start_date),'No backups') AS last_backup_time
           ,D.recovery_model_desc
           ,state_desc,
            CASE WHEN type ='D' THEN 'Full database'
            WHEN type ='I' THEN 'Differential database'
            WHEN type ='L' THEN 'Log'
            WHEN type ='F' THEN 'File or filegroup'
            WHEN type ='G' THEN 'Differential file'
            WHEN type ='P' THEN 'Partial'
            WHEN type ='Q' THEN 'Differential partial'
            ELSE 'Unknown' END AS backup_type
           ,physical_device_name
FROM        sys.databases D
LEFT JOIN  (
    SELECT  database_name,backup_start_date,type,physical_device_name
           ,Row_Number() OVER(PARTITION BY database_name,BS.type
            ORDER BY backup_start_date DESC) AS RowNum
    FROM    msdb..backupset BS
    JOIN    msdb.dbo.backupmediafamily BMF
      ON    BS.media_set_id=BMF.media_set_id
) AS CTE
   ON       D.name = CTE.database_name
  AND       RowNum = 1
ORDER BY    D.name,type;

Is there any other useful information that I can display? Can you propose some additional information that I can query?

score 0 · Answer 1 · edited Apr 13 '17 at 12:42

This query will return you all the columns of both tables, you can add your desired column in second query of simple joins without CTE

SELECT *
FROM msdb.dbo.backupset s
     INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
     ORDER BY backup_start_date DESC, backup_finish_date

Query2:

Select  s.database_name, 
          m.physical_device_name,
          CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) 
            AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
          CASE s.[type]
           WHEN 'D' THEN 'Full'
           WHEN 'I' THEN 'Differential'
           WHEN 'L' THEN 'Transaction Log'
          END AS BackupType,
          s.server_name,
          s.recovery_model
     FROM msdb.dbo.backupset s
     INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
     ORDER BY backup_start_date DESC, backup_finish_date
     GO

Reference Link

SQL query for SQL Server backups

1 Answers1