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

1291人阅读 评论(0) 收藏 举报
分类:

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


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'


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1033280次
    • 积分:13865
    • 等级:
    • 排名:第897名
    • 原创:301篇
    • 转载:105篇
    • 译文:4篇
    • 评论:522条
    文章分类
    最新评论