一般我们想了解一个数据库是否曾经备份过，或者最后一次备份是什么时候，这个时候有两种方法可以查看：
方法一，通过界面方式,选中数据库右键点击属性，在常规选项就可以看到上一次数据和日志备份的时间。
方法二：第一种方法很直观操作也很简单，但是这种方法存在局限性，每次只能看一个库，那第二种方法是可以查看某个实例下的所有数据库的备份情况。
-------------------------查询某个实例下所有数据库最近一次备份的时间---------------------------------;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_descfrom sys.databases as dleft join CTE_Last_Full_Backup as f on d.name=f.database_name and f.rank=1left join CTE_Last_Log_Backup as l on d.name=l.database_name and l.rank=1left join CTE_Last_diff_Backup as c on d.name=c.database_name and c.rank=1 order by d.name--------------------------查看某个数据库的备份历史信息------------------------------------------------------select top 100database_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
方法三：exec sp_readerrorlog 通过查看日志，查看是否有备份的记录。