SQL Server Database Backup Info

There are two scripts one for getting only backup dates of each database and other one also fetch backup size.

First ,

Use [Master]

Go

select

S.name as [DatabaseName],

S.recovery_model_desc as [RecoveryModel],

S.state_desc [DatabaseState],

S.create_date as [DatabaseCreatedDate],

b.[Last_Full_Backup_Date] as [LastFullBackupDate],

L.Last_Log_Backup_Date as [LastLogBackupDate],

I.[Last_Diff_Backup_Date] as [LastDiffBackupDate]

from

sys.databases S

left outer join

(select database_name, max(backup_finish_date) as [Last_Full_Backup_Date] from msdb.dbo.backupset

Where type='D'

group by database_name) b on ( s.name =b.database_name)

Left outer join

(select database_name, max(backup_finish_date) as [Last_Log_Backup_Date] from msdb.dbo.backupset

Where type='L'

group by database_name) L on ( s.name =L.database_name)

Left outer join

(select database_name, max(backup_finish_date) as [Last_Diff_Backup_Date] from msdb.dbo.backupset

Where type='I'

group by database_name) I on ( s.name =I.database_name)

order by

s.name

Second,

Use [Master]

Go

select

S.name as [DatabaseName],

S.recovery_model_desc as [RecoveryModel],

S.state_desc [DatabaseState],

S.create_date as [DatabaseCreatedDate],

F.[Last_Full_Backup_Date] as [LastFullBackupDate],

F.backup_size,

L.Last_Log_Backup_Date as [LastLogBackupDate],

L.backup_size as [LogBackupSize],

I.[Last_Diff_Backup_Date] as [LastDiffBackupDate],

I.backup_size as [DiffBackupSize]

from

sys.databases S

outer apply

(select top 1 database_name, backup_finish_date as [Last_Full_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='D' and database_name =s.name order by backup_finish_date desc ) F

outer apply

(select top 1 database_name, backup_finish_date as [Last_Log_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='L' and database_name =s.name order by backup_finish_date desc ) L

outer apply

(select top 1 database_name, backup_finish_date as [Last_Diff_Backup_Date] , backup_size from msdb.dbo.backupset

Where type='I' and database_name =s.name order by backup_finish_date desc ) I

order by

s.name