sqlserver查看备份历史记录

select * from [msdb].[dbo].[backupset];

SELECT [filegroup_name]
      ,[backed_up_page_count]
      ,[file_type]
      ,[file_size]
      ,[logical_name]
      ,[physical_name]
  ,State
      ,[state_desc]
      ,[backup_size]
  ,[differential_base_lsn]
 FROM [msdb].[dbo].[backupfile];

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
When 'I' THEN 'Differential database'
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1 ) 
ORDER BY 
msdb.dbo.backupset.backup_finish_date desc

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name

SELECT      
      S.NAME AS database_name,  
      'Nobackups' AS [Backup Age (Hours)]  
FROM 
   master.dbo.sysdatabases S LEFT JOIN msdb.dbo.backupset B
       ON S.name  = B.database_name 
WHERE B.database_name IS NULL AND S.name <> 'tempdb'
ORDER BY  
   B.database_name

	参考 https://www.sqlshack.com/backup-and-restore-or-recovery-strategies-for-sql-server-database/
    
    SELECT
    [database_name] AS DatabaseName,
    MAX(YEAR(backup_start_date) * 100 
            + MONTH(backup_start_date)) AS YearMonth ,
    CONVERT(numeric(10, 1), AVG([backup_size]/1024/1024)) AS BackupSizeMB,
    CONVERT(numeric(10, 1), AVG([compressed_backup_size]/1024/1024)) AS Compressed_BackupSizeMB,
    CONVERT(numeric(10, 1), AVG([backup_size]/[compressed_backup_size])) AS Compres_sionRatio
    FROM msdb.dbo.backupset 
    WHERE 1=1
    and [database_name] = N'Sbtest'
    AND [type] = 'D' 
    and backup_start_date BETWEEN DATEADD(mm, - 6, getdate() ) AND getdate()
    GROUP BY [database_name],DATEPART(mm,[backup_start_date])
    Order by YearMonth desc;

    WITH HISTORY AS 
    (SELECT DBS.database_name AS DBName 
            ,YEAR(DBS.backup_start_date) * 100 
            + MONTH(DBS.backup_start_date) AS YearMonth 
            ,CONVERT(numeric(10, 1), MIN(DBF.file_size / 1048576.0)) AS MinMB 
            ,CONVERT(numeric(10, 1), MAX(DBF.file_size / 1048576.0)) AS MaxMB 
            ,CONVERT(numeric(10, 1), AVG(DBF.file_size / 1048576.0)) AS AvgMB 
        FROM msdb.dbo.backupset as DBS 
            INNER JOIN 
            msdb.dbo.backupfile AS DBF 
                ON DBS.backup_set_id = DBF.backup_set_id 
        WHERE NOT DBS.database_name IN 
                ('master', 'msdb', 'model', 'tempdb') 
            AND DBF.file_type = 'D' 
            AND DBS.backup_start_date BETWEEN DATEADD(mm, - 6, getdate() ) AND  getdate()
        GROUP BY DBS.database_name 
                ,YEAR(DBS.backup_start_date) 
                ,MONTH(DBS.backup_start_date)) 
    SELECT H.DBName 
        ,H.YearMonth 
        ,H.MinMB 
        ,H.MaxMB 
        ,H.AvgMB 
        ,H.AvgMB  
        - (SELECT TOP 1 H1.AvgMB 
            FROM HISTORY AS H1 
            WHERE H1.DBName = H.DBName 
                    AND H1.YearMonth < H.YearMonth 
            ORDER BY H1.YearMonth DESC) AS DBGrowthMB 
    FROM HISTORY AS H 
    ORDER BY H.DBName 
            ,H.YearMonth;