0

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.

2
  • 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? Apr 13 '16 at 13:01
  • 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.
    – SQLPRODDBA
    Apr 13 '16 at 13:05
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;
2
  • 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
    Apr 13 '16 at 13:23
  • Or have to put in same details in other columns in case we have 4 FULL and 6 DIFF backup like scenario?
    – SQLPRODDBA
    Apr 13 '16 at 13:30
1

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
1
  • 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.
    – SQLPRODDBA
    Apr 13 '16 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.