How To Find Last Backup Dates Of SQL Server Databases

dbtut

6 years ago

With the help of the following script, you can find the date of the last backup of each database on that instance.

‘L’ indicates Log Backup. If you type ‘D’ in this section, you can see the last full backup date, if you type ‘G’, you can see the latest differential backup date.

If your database is included with AG, you can see the correct result when running this script on both nodes. Because the backup could be taken over the other node.

SELECT   sd.name,
         MAX(bs.backup_finish_date) AS backup_finish_date
FROM     master.sys.sysdatabases sd
         LEFT OUTER JOIN msdb..backupset bs
         ON bs.database_name = sd.name
         AND bs.type = 'L'
GROUP BY sd.name
ORDER BY backup_finish_date DESC

SELECT sd.name,

MAX(bs.backup_finish_date) AS backup_finish_date

FROM master.sys.sysdatabases sd

LEFT OUTER JOIN msdb..backupset bs

ON bs.database_name = sd.name

AND bs.type = 'L'

GROUP BY sd.name

ORDER BY backup_finish_date DESC