Need help with this backup audit SQL Server script

Question

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 example, if I have 4 split FULL backup files and 4 split diff backup files, 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 example, 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]); 

Edit:

Let's say I am taking my Full and Diff backups as below:

backup database AdventureWorks to disk =
'D:\MSSQL\AdventureWorks_1_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_2_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_3_041320161807.bak', disk =
'D:\MSSQL\AdventureWorks_4_041320161807.bak' with stats = 10;
backup database AdventureWorks
to disk = 'D:\MSSQL\AdventureWorks_1_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_2_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_3_041320161808.bak',
disk = 'D:\MSSQL\AdventureWorks_4_041320161808.bak'
with stats = 10, differential; 

With the above script I am getting result as below. Notice that it has 16 rows. 4*4.

enter image description here

What I need is, the script should show output as 4 Full backup files and 4 diff backup files.i.e. only the actual number of file. Currently due to JOIN it is giving 16 rows. Please let me know if you need any further clarification.

• spaghettidba: In my opinion, it doesn't make much sense. How are you correlating the 1st full backup file with the 1st diff backup file? And what happens if you have 6 diffs and 4 fulls?
• SQLPRODDBA: You are 100% right! What I was trying to accomplish was, it should show actual files and wherever there are cases with different no. of files like you said, it should NULLs apart from actual files in the output. i.e 4 fulls + 2 NULLS = 6 diffs. I am not sure if this is possible.

Answer 1

You need to add a new column to your subqueries to calculate the ROW_NUMBER() in addition to the RANK(), so that you can match rows with the same ROW_NUMBER().

Moreover, you need to change the LEFT JOINs to FULL JOINs:

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),
 [RN] = ROW_NUMBER() 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),
 [RN] = ROW_NUMBER() 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),
 [RN] = ROW_NUMBER() 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
 FULL OUTER JOIN lastfull 
 ON db.[Database] = lastfull.[Database]
 FULL OUTER JOIN lastdiff 
 ON db.[Database] = lastdiff.[Database]
 AND lastfull.RN = lastdiff.RN
 FULL OUTER JOIN lastlog 
 ON db.[Database] = lastlog.[Database]
 AND lastfull.RN = lastlog.RN;

• SQLPRODDBA: Awesome! Thanks a ton! We are very close now except for the fact that script is now adding extra results from outer tables due to FULL join. I guess we can remove it by adding where clause in the final query right?
• SQLPRODDBA: Or have to put in same details in other columns in case we have 4 FULL and 6 DIFF backup like scenario?

Answer 2

alternatively to the answer from spaghettidba, I have been using this script, it does not have so much information, but could be added if needed.

it seems to be quicker, and you can specify whether you want full or log backups to be shown.

mainly I used it to find out these questions: do we have a backup, and where is it, and how big is it, and how long it usually takes to backup.

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 (@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

• SQLPRODDBA: Thanks for your reply! What I was trying here is to have all the backups listed in one place with one script only and use it to make a company-wide report for all servers.