View Database Backup Status

Generally we want to know if a database has been backed up, or when the last backup was made, there are two ways to see it:

Method one, through the interface, check the database right-click Properties, the General option to see the last data and log backup time.

Method Two: The first method is very intuitive operation is also very simple, but this method has limitations, each time can only see a library, the second method is to view the backup of all databases under an instance.

 -------------------------Query the time of the last backup of all databases under an instance---------------------------------; withcte_last_full_backup (Database_name,backup_start_date,rank) as(   Selectdatabase_name,backup_start_date, rank () Over(Partition bydatabase_nameOrder  byBackup_start_datedesc) asRank fromMsdb.dbo.backupsetwhereType='D'  --type= ' D ' represents a full backup, while Type= ' L ' represents a log backup, and for a differential backup, it is type= ' I '. ), Cte_last_log_backup (Database_name,backup_start_date,rank) as(   Selectdatabase_name,backup_start_date, rank () Over(Partition bydatabase_nameOrder  byBackup_start_datedesc) asRank fromMsdb.dbo.backupsetwhereType='L'), Cte_last_diff_backup (Database_name,backup_start_date,rank) as(    Selectdatabase_name,backup_start_date, rank () Over(Partition bydatabase_nameOrder  byBackup_start_datedesc) asRank fromMsdb.dbo.backupsetwhereType='l=i')SelectD.name asdatabase_name,f.backup_start_date aslast_full_backup,l.backup_start_date aslast_log_backup,c.backup_start_date asLast_diff_backup_time, Recovery_model_desc fromsys.databases asD Left JoinCte_last_full_backup asF onD.name=F.database_name andF.rank=1 Left JoinCte_last_log_backup asL onD.name=L.database_name andL.rank=1 Left JoinCte_last_diff_backup asC onD.name=C.database_name andC.rank=1 Order  byD.name--------------------------View the backup history information for a database------------------------------------------------------Select Top  -Database_name,database_creation_date,backup_start_date,backup_finish_date, Case  whenType='D'  Then 'Full_backup'      whenType='L'  Then 'Log_backup'      whenType='I'  Then 'Differental_backup'     Else 'NULL'     End   asBackup_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] fromMsdb.dbo.backupset with(NOLOCK)wheredatabase_name='ipp3_history'Order  byBackup_start_datedesc

View database backup conditions