Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 | 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 |
