SQL Server Backup Report

Question

I'm trying to create a report that details the latest backups on our SQL Servers. As of right now I have this written out:

select 
    [Server_Name] = a.server_name, 
    [Database_Name] = a.database_name, 
    [Last_Backup] = max(a.backup_finish_date),  
    [Backup_Type] = CASE A.type
                              WHEN 'D' THEN 'FULL'
                              WHEN 'I' THEN 'Differential'
                              WHEN 'L' THEN 'Log'
                              WHEN 'F' THEN 'FileGroup'
                              WHEN 'G' THEN 'FileGroup Differential'
                              WHEN 'P' THEN 'Partial'
                              WHEN 'Q' THEN 'Partial Differential'
                  END,
    [Backup_Set] = b.name,
    [Days_Since_Last_Backup] = DATEDIFF(d,(max(a.backup_finish_Date)),GETDATE())
from msdb.dbo.backupset AS a
INNER JOIN msdb.dbo.backupset as b ON a.backup_set_id = b.backup_set_id
GROUP BY a.database_name, a.server_name, b.name, a.type
ORDER BY database_name

I want to change it so that the columns to display the following:

Server_Name, Database_Name, Last_Full_Backup, Last_Diff_Backup, Last_Log_Backup, 
Backup_Set, Days_Since_Last_Full

In order to display the dates for Last_Full_Backup, Last_Diff_Backup, and Last_Log_Backup I think I have to do some pivoting, but I'm not really sure how (I'm very unfamiliar with pivoting).

Any help would be greatly appreciated. Thanks to all in advance.

Joachim Isaksson · Accepted Answer · 2014-03-04T18:47:23.267

0

You could do a "manual pivot", something like;

SELECT 
    [Server_Name] = a.server_name, 
    [Database_Name] = a.database_name, 
    [Last_Backup]      = MAX(a.backup_finish_date),  
    [Last_Full_Backup] = MAX(CASE WHEN A.type='D' 
                                    THEN a.backup_finish_date ELSE NULL END),
    [Last_Diff_Backup] = MAX(CASE WHEN A.type='I' 
                                    THEN a.backup_finish_date ELSE NULL END),
    [Last_Log_Backup]  = MAX(CASE WHEN A.type='L' 
                                    THEN a.backup_finish_date ELSE NULL END),
    [Backup_Set] = b.name,
    [Days_Since_Last_Backup] = DATEDIFF(d,(max(a.backup_finish_Date)),GETDATE())
FROM msdb.dbo.backupset AS a
INNER JOIN msdb.dbo.backupset as b ON a.backup_set_id = b.backup_set_id
GROUP BY a.database_name, a.server_name
ORDER BY database_name

edited Mar 04 '14 at 18:47

answered Mar 04 '14 at 18:16

Joachim Isaksson

176,943
25
281
294

That looks great so far! I can add to the "CASE" statement to handle the NULLS. How would you propose I make the [Days_Since_Last_Backup] only display days since the last FULL? I'd change the column to read [Days_Since_Last_Full]... – homerj742 Mar 04 '14 at 18:39
I also want to try to keep it one database per line. This query returns 2 DB's per line. – homerj742 Mar 04 '14 at 18:45
@user3380003 The `GROUP BY` included the type, which should not have been there, removed that. Not sure if the backup set name should be included or not since I'm not sure of its use. – Joachim Isaksson Mar 04 '14 at 18:48
...and `DATEDIFF(d,(MAX(CASE WHEN A.type='D' THEN a.backup_finish_date ELSE NULL END)),GETDATE())` _should_ give you days since last full backup. – Joachim Isaksson Mar 04 '14 at 18:59
1

Thank you Joachim, removing the group by for type got me the report I wanted to see. The reason I wanted the Backup_Set is that we want to know what type of backup our databases are doing (ie- Commvault, Netapp, or sql native). – homerj742 Mar 04 '14 at 19:10

SQL Server Backup Report

1 Answers1