I am using below backup script taken from http://www.ludovicocaldara.net/dba/sql-server-last-database-backup/ link to audit my servers for backups. This works great except when I have spilt backups. For splitbackups it gives lots of rows depending on no. of split backup files. For e.g. If I have 4 spilt FULL backup files and 4 split diff backup file, it gives 16 rows of output. This is because of the left join used. I need help in fixing the script to remove all duplicates and provide correct output. For e.g. it should only give 4 split FULL backup files and 4 diff backup files in output.

WITH db AS (SELECT [Instance] = @@SERVERNAME, [Database] = name, [RecoveryMode] = DATABASEPROPERTYEX(name, 'Recovery'), [CreationTime] = crdate, [Status] = DATABASEPROPERTYEX(name, 'Status') FROM master..sysdatabases WHERE name != 'tempdb'), lastfull AS (SELECT * FROM ( SELECT [Database] = s.database_name, --[Type] = s.type, [LastFullDate] = CONVERT( VARCHAR, s.backup_finish_date, 120), --[LastFullSize] = s.backup_size, [LastFullDevice] = f.physical_device_name, --[LastFullDevTyp] = f.device_type, [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC) FROM msdb.dbo.backupset s, msdb.dbo.backupmediafamily f WHERE s.media_set_id = f.media_set_id AND s.type = 'D' -- and f.device_type = 7 -- only backup devices ) f WHERE nrank = 1), lastdiff AS (SELECT * FROM ( SELECT [Database] = s.database_name, --[Type] = s.type, [LastDiffDate] = CONVERT( VARCHAR, s.backup_finish_date, 120), --[LastDiffSize] = s.backup_size, [LastDiffDevice] = f.physical_device_name, --[LastDiffDevTyp] = f.device_type, [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC) FROM msdb.dbo.backupset s, msdb.dbo.backupmediafamily f WHERE s.media_set_id = f.media_set_id AND s.type = 'I' -- and f.device_type = 7 -- only backup devices ) d WHERE nrank = 1), lastlog AS (SELECT * FROM ( SELECT [Database] = s.database_name, --[Type] = s.type, [LastLogDate] = CONVERT( VARCHAR, s.backup_finish_date, 120), --[LastLogSize] = s.backup_size, [LastLogDevice] = f.physical_device_name, --[LastLogDevTyp] = f.device_type, [Nrank] = RANK() OVER(PARTITION BY s.database_name ORDER BY s.backup_finish_date DESC) FROM msdb.dbo.backupset s, msdb.dbo.backupmediafamily f WHERE s.media_set_id = f.media_set_id AND s.type = 'L' -- and f.device_type = 7 -- only backup devices ) l WHERE nrank = 1) SELECT db.[Instance] AS ServerName, db.[Database] AS DatabaseName, db.[RecoveryMode] AS ReconveyModel, db.[CreationTime] AS DBCreationTime, db.[Status] AS DatabaseStatus, ISNULL(CONVERT( VARCHAR, lastfull.LastFullDate), 'Never') AS LastFullBackupDate, ISNULL(lastfull.[LastFullDevice], 'None') AS LastFullBackupPath, ISNULL(CONVERT( VARCHAR, lastdiff.LastDiffDate), 'Never') AS LastDiffBackupDate, ISNULL(lastdiff.[LastDiffDevice], 'None') AS LastDiffBackupPath, ISNULL(CONVERT( VARCHAR, lastlog.[LastLogDate]), 'Never') AS LastLogBackupDate, ISNULL(lastlog.[LastLogDevice], 'None') AS LastLogBackupPath FROM db LEFT OUTER JOIN lastfull ON(db.[Database] = lastfull.[Database]) LEFT OUTER JOIN lastdiff ON(db.[Database] = lastdiff.[Database]) LEFT OUTER JOIN lastlog ON(db.[Database] = lastlog.[Database]);