sp_BackupListing

Description

This stored procedure will list all databases and their respective backups.   

Code

CREATE Procedure [dbo].[sp_BackupListing] 

  (@LegacyDB VARCHAR(10) = 'No' )

AS

-- copyright Eric Peterson, All rights reserved, and as is with no warranty


SET NOCOUNT on

SET ANSI_WARNINGS off


declare @type varchar(20)  

declare @DBIn varchar(100) 

declare @options varchar(100) 


-- Get the last full backup from the backupset for all databases including dropped (legacy) databases and databases that have no backups 


SELECT  ISNULL(d.name, ( s.database_name + ' (Legacy)' )) AS 'DBName' ,

        MAX(s.backup_start_date) AS 'LastFullBackup' ,

        ISNULL(d.state_desc, 'Disconnected') AS 'DBState' ,

        CASE 

             WHEN MAX(s.backup_start_date) IS NULL 

THEN 'No Backup!'

WHEN MAX(s.backup_start_date) > DATEADD(DAY, -1, GETDATE()) --> backups older than 24 hours

THEN 'Current'

WHEN d.state_desc = 'OFFLINE'

THEN 'Unknown' 

-- WHEN  MAX(s.backup_start_date) > DATEADD(DAY, -7, getdate()) 

-- THEN 'Warning'

             ELSE 'Failed'

        END AS 'Status'

INTO    #BU_ListingFull

FROM    msdb.dbo.backupset s

FULL OUTER JOIN master.sys.databases d 

ON d.name = s.database_name

WHERE   ( s.type = 'D'

          OR s.type IS NULL )

        AND ISNULL(d.name, ( s.database_name )) NOT IN ( 'tempdb',

                                                         'distribution' )

GROUP BY ISNULL(d.name, ( s.database_name + ' (Legacy)' )) ,

        d.state_desc --, dev.logical_device_name, dev.physical_device_name

ORDER BY 2;


-- Get the last DIFF backup from the backupset for all databases including dropped (legacy) ones

--SELECT * FROM msdb.dbo.backupset s 

--SELECT * FROM sys.databases


select isnull(d.name, (s.database_name + ' (Legacy)')) as 'DBNameDiff',

max(s.backup_start_date)                        AS 'LastDiffBackup',

case  

when max(s.backup_start_date) > dateadd(day, -1, getdate())  --> backups older than 24 hours

then 'Current'

when max(s.backup_start_date) is Null 

then 'No Diff'

else NULL

end as 'DiffStatus'

into #BU_ListingDiff

From msdb.dbo.backupset s 

full outer join master.dbo.sysdatabases d

on d.name = s.database_name 

where ( s.type = 'I' ) 

 and isnull(d.name, (s.database_name)) not in ('tempdb', 'distribution')

group by isnull(d.name, (s.database_name + ' (Legacy)')) --, dev.logical_device_name, dev.physical_device_name

order by 2


-- Get the last Log backup from the backupset for all databases including dropped (legacy) ones



select isnull(d.name, (s.database_name + ' (Legacy)')) as 'DBNameLog',

max(s.backup_start_date)                        AS 'LastLogBackup',

'Unknown' AS 'LogStatus'

into #BU_ListingLog

From msdb.dbo.backupset s 

full outer join master.dbo.sysdatabases d

on d.name = s.database_name 

where ( s.type = 'L' ) 

 and isnull(d.name, (s.database_name)) not in ('tempdb', 'distribution')

group by isnull(d.name, (s.database_name + ' (Legacy)')) --, dev.logical_device_name, dev.physical_device_name

order by 2



--decide TO Display legacy DATABASE backups IN list


IF @LegacyDB = 'No'

SET @LegacyDB = 'Legacy'


select @@ServerName AS 'ServerName', 

Fbk.DBName AS 'DatabaseName', 

CASE Fbk.Status

when 'Failed' 

   then case 

    when FBk.DBName like '%Legacy%' then 'N/A'

when Diff.DiffStatus = 'Current'   then 'CkDiff'

else 'Failed'

end

    else Fbk.Status

end                                             AS 'BkupStatus',

case  

when Diff.LastDiffBackup > Fbk.lastFullBackup 

    then Diff.DiffStatus 

                when Diff.LastDiffBackup < Fbk.lastFullBackup 

    then 'N/A'

else 'No Diff'

end AS 'DiffStatus',

case  

                WHEN s.recovery_model = 'SIMPLE'

THEN 'N/A'

WHEN LogBk.LastLogBackup > dateadd( HOUR, -2, getdate()) 

then 'Current'

when logBk.LastLogBackup is Null 

then 'No Log!'

WHEN LogBk.LastLogBackup < Fbk.LastFullBackup 

then 'No Log!'

else LogBk.LogStatus

end AS 'LogStatus',

Fbk.LastFullBackup AS 'LastFullBackup',

s.backup_size AS 'BackupSize',

FBK.DBState                                         AS 'DBState',

s.recovery_model AS 'RecoveryModel',

CONVERT(varchar(4), software_major_version ) + '.' +

CONVERT(varchar(4),software_minor_version) + '.' +

CONVERT(varchar(10),software_build_version) + '.' +

CONVERT(VARCHAR(4), mtf_minor_version)              AS 'SoftwareVersion',

diff.LastDiffBackup AS 'LastDiffBackup',

LogBk.LastLogBackup AS 'LastLogBackup',

dev.physical_device_name AS 'BackupLocation' ,

isnull( dev.logical_device_name,' ' ) AS 'BackupDeviceUsed' ,

bd.name AS 'BackupDeviceName',

bd.phyname AS 'BackupDeviceLocation'

--INTO BU_Listing    

FROM #BU_ListingFull FBk

left outer join msdb.dbo.backupset s

on  REPLACE(Fbk.DBName,' (Legacy)', '') = s.database_name

and FBk.LastFullBackup = s.backup_start_date

left outer join msdb.dbo.backupmediafamily dev

ON s.media_set_id = dev.media_set_id

left outer join master.dbo.sysdevices bd

on 'bu_' + Fbk.DBName = bd.name

left outer join #BU_ListingLog LogBk

on  Fbk.DBName = LogBk.DBNameLog

left outer join #BU_ListingDiff Diff

on  Fbk.DBName = Diff.DBNameDiff

WHERE Fbk.DBName NOT LIKE '%(' + @LegacyDB + ')%'

Order by Fbk.DBName



drop table #BU_ListingFull

drop Table #BU_ListingDiff

drop Table #BU_Listinglog




--SELECT * FROM sys.databases

GO

Enjoy

If you can think of any improvements, let me know.