In this post, I am sharing a script to find status and last backup time of all databases in SQL Server.
The Database Backup is one of the important tasks for the DBA.
In a big integrated system, there are lots of database and DBA already set some automation and manual process for taking a backup.
But sometimes periodically, it is required to check the status of Database Backup because many times I found that my SQL Server maintenance plan did not execute properly because of any issue with: network, hard-disk.
Using this script DBA can easily find the status of Database Backup.
WHEN MAX(B.backup_finish_date) IS NULL
THEN 'Backup not taken'
END AS LastBackupDate
FROM sys.databases AS D
LEFT OUTER JOIN msdb.dbo.backupset AS B
ON D.name = B.database_name AND B.type = 'D'
GROUP BY D.name
ORDER BY 2 DESC