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.
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.
Best Answer
You need to add a new column to your subqueries to calculate the
ROW_NUMBER()
in addition to theRANK()
, so that you can match rows with the sameROW_NUMBER()
.Moreover, you need to change the
LEFT JOIN
s toFULL JOIN
s: