查看数据库备份情况

 
-------------------------查询某个实例下所有数据库最近一次备份的时间---------------------------------
;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‘  --type=‘D‘表示全备份，而type=‘L‘表示日志备份，至于差异备份，则是type=‘I‘。
)
,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‘
)
,CTE_Last_diff_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=I‘
)

select d.name as database_name,
f.backup_start_date as last_full_backup,
l.backup_start_date as last_log_backup,
c.backup_start_date as Last_diff_backup_time 
,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
left join CTE_Last_diff_Backup as c on d.name=c.database_name and c.rank=1 
order by d.name

--------------------------查看某个数据库的备份历史信息------------------------------------------------------
select top 100
database_name,
database_creation_date,
backup_start_date,
backup_finish_date,
case when type=‘D‘ then ‘full_backup‘
     when type=‘L‘ then ‘log_backup‘
     when type=‘I‘ then ‘differental_backup‘
     else ‘NULL‘
     end  as backup_type,
(backup_size/1024.0/1024.0) as [backup_size(MB)],
(compressed_backup_size/1024.0/1024.0) as  [compressed_backup_size(MB)],
[user_name]
from msdb.dbo.backupset with(nolock)
where database_name=‘IPP3_History‘
order by  backup_start_date desc