Xrhrft

I'm using the below T-SQL query to determine the date of the last full database backup and also return the size and location of the backup file. My problem is that it won't return any data at all for databases that have had no backups or for which there is no backup history for. Ideally, I'd want to modify the query so that all databases are returned, regardless of whether or not there's any backup history for them. Can anyone advise how the below query could be modified to accommodate this?

WITH LastBackUp AS

(

SELECT  bs.database_name,

        bs.backup_size,

        bs.backup_start_date,

        bmf.physical_device_name,

        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )

FROM  msdb.dbo.backupmediafamily bmf

JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id

JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id

WHERE   bs.[type] = 'D'

AND bs.is_copy_only = 0

)

SELECT 

        database_name AS [Database],

        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],

        backup_start_date AS [Last Full DB Backup Date],

        physical_device_name AS [Backup File Location]

FROM LastBackUp

WHERE Position = 1

ORDER BY [Database];

You need to query the list of databases first (it's in sys.databases), then join it to your query:

WITH LastBackUp AS

(

SELECT  bs.database_name,

        bs.backup_size,

        bs.backup_start_date,

        bmf.physical_device_name,

        Position = ROW_NUMBER() OVER( PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC )

FROM  msdb.dbo.backupmediafamily bmf

JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id

JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id

WHERE   bs.[type] = 'D'

AND bs.is_copy_only = 0

)

SELECT 

        sd.name AS [Database],

        CAST(backup_size / 1048576 AS DECIMAL(10, 2) ) AS [BackupSizeMB],

        backup_start_date AS [Last Full DB Backup Date],

        physical_device_name AS [Backup File Location]

FROM sys.databases AS sd

LEFT JOIN LastBackUp AS lb

    ON sd.name = lb.database_name

    AND Position = 1

ORDER BY [Database];

Just to add to the beautiful answer by Spaghettidba:

what if you backup to more than one file?
this script will show you it all.

        declare @backup_type CHAR(1) = 'D' --'D' full, 'L' log

        ;with Radhe as (

            SELECT  @@Servername as [Server_Name],

            B.name as Database_Name, 

            ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,

            ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate

            ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))

            ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))

            ,media_set_id = MAX(A.media_set_id)

            ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))

            ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))

            ,A.type

            FROM sys.databases B 



            LEFT OUTER JOIN msdb.dbo.backupset A 

                         ON A.database_name = B.name 

                        AND A.is_copy_only = 0

                        AND (@backup_type IS NULL OR A.type = @backup_type  )



            GROUP BY B.Name, A.type



        )



         SELECT r.[Server_Name]

               ,r.Database_Name

               ,[Backup Type] = r.type 

               ,r.DaysSinceLastBackup

               ,r.LastBackupDate

               ,r.BackupSize_GB

               ,r.BackupSize_MB

               ,F.physical_device_name

               ,r.[AVG Backup Duration]

               ,r.[Longest Backup Duration]



           FROM Radhe r



            LEFT OUTER JOIN msdb.dbo.backupmediafamily F

                         ON R.media_set_id = F.media_set_id



            ORDER BY r.Server_Name, r.Database_Name