1

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?

  • what is your objective? – AA.SC Jan 9 '16 at 12:31
  • I want to execute this query in order to get as much as possible data for backups – Peter Penzov Jan 9 '16 at 12:55
  • Why don't you take an overview of MSDB backup tables from link msdn.microsoft.com/en-us/library/ms186299.aspx. and select columns according to your requirement. – AA.SC Jan 9 '16 at 13:02
  • I don't have experience with SQL and SQL server. I can add more columns into the SELECT from how I need to extend LEFT JOIN AS CTE and etc.? – Peter Penzov Jan 9 '16 at 13:03
0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.