Many times while trouble shooting backup issues on SQL server we have to check manually that when was the latest backup taken , where it was taken and for which all databases it happened . This requires a lot of manual clicks and script typing.
To ease this , I have developed a script that gives comprehensive visual result of all the required backup information needed to get the current situation of the backups on that SQL server. You can automate it in PowerShell to send you monthly or weekly reports as well.
IF OBJECT_ID('tempdb..#Backup', 'U') IS NOT NULL
DROP TABLE tempdb..#Backup;
SELECT DISTINCT a.Name AS DatabaseName
,CONVERT(SYSNAME, DATABASEPROPERTYEX(a.name, 'Recovery')) RecoveryModel
,COALESCE((
SELECT CONVERT(VARCHAR(max), MAX(backup_finish_date))
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'D'
--AND is_copy_only = '0'
), 'No Full') AS 'FullBakDate'
,COALESCE((
SELECT CONVERT(VARCHAR(max), MAX(backup_finish_date)) --, 101)
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'I'
--AND is_copy_only = '0'
), 'No Diff') AS 'DiffBakDate'
,COALESCE((
SELECT CONVERT(VARCHAR(max), MAX(backup_finish_date)) -- , 120)
FROM msdb.dbo.backupset
WHERE database_name = a.name
AND type = 'L'
), 'No Log') AS 'LastLogBakDate'
INTO #Backup
FROM sys.databases a
LEFT OUTER JOIN msdb.dbo.backupset b ON b.database_name = a.name
WHERE a.name <> 'tempdb'
AND a.state_desc = 'online'
GROUP BY a.Name
,a.compatibility_level
ORDER BY a.name
IF OBJECT_ID('tempdb..#Bpath', 'U') IS NOT NULL
DROP TABLE tempdb..#Bpath;
SELECT DatabaseName = x.database_name
,LastBackupFileName = x.physical_device_name
,LastBackupDatetime = x.backup_start_date
INTO #Bpath
FROM (
SELECT bs.database_name
,bs.backup_start_date
,bmf.physical_device_name
,Ordinal = ROW_NUMBER() OVER (
PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC
)
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = 'D'
--AND bs.is_copy_only = 0
) x
WHERE x.Ordinal = 1
ORDER BY DatabaseName;
-----------------------------
IF OBJECT_ID('tempdb..#Diffpath', 'U') IS NOT NULL
DROP TABLE tempdb..#Diffpath;
SELECT DatabaseName = x.database_name
,LastBackupFileName = x.physical_device_name
,LastBackupDatetime = x.backup_start_date
INTO #Diffpath
FROM (
SELECT bs.database_name
,bs.backup_start_date
,bmf.physical_device_name
,Ordinal = ROW_NUMBER() OVER (
PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC
)
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = 'I'
--AND bs.is_copy_only = 0
) x
WHERE x.Ordinal = 1
ORDER BY DatabaseName;
-----------------------------
IF OBJECT_ID('tempdb..#Lpath', 'U') IS NOT NULL
DROP TABLE tempdb..#Lpath;
SELECT DatabaseName = x.database_name
,LastBackupFileName = x.physical_device_name
,LastBackupDatetime = x.backup_start_date
INTO #Lpath
FROM (
SELECT bs.database_name
,bs.backup_start_date
,bmf.physical_device_name
,Ordinal = ROW_NUMBER() OVER (
PARTITION BY bs.database_name ORDER BY bs.backup_start_date DESC
)
FROM msdb.dbo.backupmediafamily bmf
JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
WHERE bs.[type] = 'L'
-- AND bs.is_copy_only = 0
) x
WHERE x.Ordinal = 1
ORDER BY DatabaseName;
------------------------
SELECT @@servername AS SQLInstance
,bk.DatabaseName
,bk.RecoveryModel
,[bk].[FullBakDate]
,bk.[DiffBakDate]
,bk.[LastLogBakDate]
,bp.LastBackupFileName AS FullBackupFile
,dp.LastBackupFileName AS DiffBackkupFile
,lk.LastBackupFileName AS LogBackupFile
,getdate() AS ReportCollectionDate
FROM #Backup bk
INNER JOIN #Bpath bp ON bp.DatabaseName = bk.DatabaseName
LEFT JOIN #Diffpath dp ON dp.DatabaseName = bk.DatabaseName
LEFT JOIN #Lpath lk ON lk.DatabaseName = bk.DatabaseName
