SQLCHAD – Life as a SQL Server DBA

Here is a handy script I created that gives you the last backup date for all databases on a SQL instance.  This can be incorporated into powershell to get the last backup date from all servers in your enterprise

DECLARE @DBName varchar(100)
CREATE TABLE #DatabaseBackupDate
(
DatabaseName varchar(100),
BackupDate datetime,
DaysOld int
)
DECLARE c_Databases CURSOR FOR select distinct name from master.dbo.sysdatabases where name not in ('tempdb') order by name
open c_Databases
fetch c_Databases into @DBName
while (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO #DatabaseBackupDate (DatabaseName) values(@DBName)

UPDATE #DatabaseBackupDate set BackupDate = backup_finish_date, DaysOld = DATEDIFF(day, backup_finish_date, getdate())
FROM msdb..backupset INNER JOIN #DatabaseBackupDate on msdb..backupset.database_name = #DatabaseBackupDate.DatabaseName
WHERE
backup_set_id = (
SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name = @DBName AND
[type] IN ('D','I') ) --Reports just Full or Differential, not log backups
fetch c_Databases into @DBName
END
select * from #DatabaseBackupDate
drop table #DatabaseBackupDate
close c_Databases
deallocate c_Databases