SQL Server Get Backup Status of your Databases Daily

If you have a lot of databases in your production on which backups are performed daily then you might want to know the backup status of all the available databases. Suppose you have Log Shipping kind of high availability solutions configured on your instance then this task gets more tedious.

So, I got one situation at my work and I've strived a lot to figure out this challenge. "msdb" is the system database which records the information regarding the status of available databases. The table "backupset" of "msdb" is one table which stores this information.

Here is the query to know the daily status of all the databases of your instance. This query gets the status on daily basis,

If you want to know the status of all types of backups taken today then execute this query,

SELECT @@SERVERNAME AS SERVER_NAME,

       CASE WHEN B.DATABASE_NAME IS NULL THEN D.NAME

            ELSE B.database_name END AS DATABASE_NAME,

       CASE B.TYPE WHEN 'D' THEN 'FULL'

                   WHEN 'I' THEN 'DIFFERENTIAL'

                   WHEN 'L' THEN 'TRANSACTION LOG'

                   ELSE '' END AS BACKUP_TYPE,

       CONVERT(varchar,GETDATE(),101) AS BACKUP_DATE,

       CASE WHEN 
       CONVERT(varchar,B.backup_start_date,101) = CONVERT(varchar,GETDATE(),101) 
       THEN 'YES'

       ELSE 'NO' END AS BACKUP_TAKEN

FROM sys.databases D

LEFT OUTER JOIN msdb.dbo.backupset B ON D.NAME = B.DATABASE_NAME AND CONVERT(varchar,B.backup_start_date,101) = CONVERT(varchar,GETDATE(),101)

WHERE D.DATABASE_ID > 4

This is the query I figured. If there are any changes to be made, all suggestions are welcomed.