Latest Backup and Restoration Dates on Databases

These are the small scripts which we can use to find when did your database got backup lastly and When did  your database got restored.

--The following script will bring all the databases in the server when they got last back 'd up.


SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(), 
MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A 
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

--  The following script will give you little bit more information about its restoration.
          It will give you the restoration Date, where it is restored from and also importantly the backup start date and End date.


SELECT RH.destination_database_name, RH.restore_date, BS.backup_start_date, 
  BS.backup_finish_date, BS.database_name as source_database_name, 
  BMF.physical_device_name as backup_file_used_for_restore
FROM msdb..restorehistory RH
 INNER JOIN msdb..backupset bs ON RH.backup_set_id = BS.backup_set_id
 INNER JOIN msdb..backupmediafamily BMF  ON BS.media_set_id = BMF.media_set_id
ORDER BY RH.restore_date DESC