查看数据库服务各数据库备份状态

 列出服务器上各数据库的备份情况

WITH CTE_Last_Full_Backup(database_name,backup_start_date,rank)as
  ( SELECT database_name,backup_start_date ,rank()over(partition BY database_name
                                                       ORDER BY backup_start_date DESC) AS rank
   FROM msdb.dbo.backupset
   WHERE TYPE='D' ),
     CTE_Last_Log_Backup(database_name,backup_start_date,rank)AS
  ( SELECT database_name,
           backup_start_date ,
           rank()over(partition BY database_name
                      ORDER BY backup_start_date DESC) AS rank
   FROM msdb.dbo.backupset
   WHERE TYPE='L' )
SELECT d.name AS database_name,
       f.backup_start_date AS last_full_backup,
       l.backup_start_date AS last_log_backup ,
       recovery_model_desc
FROM sys.databases AS d
LEFT JOIN CTE_Last_Full_Backup AS f ON d.name=f.database_name
AND f.rank=1
LEFT JOIN CTE_Last_Log_Backup AS l ON d.name=l.database_name
AND l.rank=1
ORDER BY d.name

语句中的type='D'表示全备份，而type='L'表示日志备份，至于差异备份，则是type='I'。