SQL Server : Last Backup / Restore date of the database.

Please find below two methods to know last backup date of SQL database.

1.       In SSMS , right click on database and check the properties. Refer  Last database backup which is available under General  tab.

2.       Query : you can query msdb..backupset table.

SELECT  database_name ,

        MAX(backup_finish_date) AS backup_finish_date

FROM    msdb..backupset

WHERE   database_name = 'Database_Name'

GROUP BY database_name

Last Restore date of database Query

                SELECT  destination_database_name ,

        MAX(restore_date) AS restore_date

FROM    msdb..restorehistory

WHERE   destination_database_name = 'Database_Name'

GROUP BY destination_database_name
 

Find the all databases restore date with SourceDB , SourceFile and Backup Date

USE msdb
GO
SELECT  DBRestored = destination_database_name ,
        RestoreDate = restore_date ,
        SourceDB = b.database_name ,
        SourceFile = physical_name ,
        BackupDate = backup_start_date
FROM    RestoreHistory h
        INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
        INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate