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.