Find the latest backup of databases in a SQL Server Instance

How to find the latest backup of the database

All the database backup information is written to MSDB database. A quick workaround in this database will give lot of information about the database backup happening in the current server.

Below query will fetch all necessary details from MSDB database.

Inputs like
changing the date at 3rd line from last
changing the database at 2nd line from last

If the filter is provided on the above mentioned then the query will return the precise information needed or else the below query will show all the backups currently resding in MSDB database.


SELECT sysdb.name, bkup.description,bkup.backup_start_date, bkup.backup_finish_date,bkmf.physical_device_name,

case when bkmf.device_type=2 then 'Temporary Disk'when bkmf.device_type=102 then 'Permanent Disk'when bkmf.device_type=5 then 'Temporary Tape'when bkmf.device_type=105 then 'Permanent Tape'when bkmf.device_type=6 then 'Temporary Pipe'when bkmf.device_type=106 then 'Permanent Pipe'when bkmf.device_type=7 then 'Veritas Backup'end as Disk_Type,case when type='D' then '** FULL **' when type='I' then 'DIFFERENTIAL'when type='L' then 'LOG'end as Backup_Type, (STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',ceiling(bkup.backup_size /1048576) as 'Size Meg' ,cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig', server_name
,user_name, sysdb.crdate,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins' ,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name join msdb.dbo.backupmediafamily bkmf ON bkup.media_set_id=bkmf.media_set_id where backup_finish_date > DATEADD(DAY, -30, (getdate())) /*Last 30 days-- AND sysdb.name = 'Mydb' */ORDER BY sysdb.name, bkup.backup_finish_date desc


Hope the above query is usefull for DBA day to day task

Revert to me if found any issues or need any help

Thanks