Archive for the ‘backup recovery’ Category

Get Last Backup Date

August 10, 2011 1 comment

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)
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
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
select * from #DatabaseBackupDate
drop table #DatabaseBackupDate
close c_Databases
deallocate c_Databases
Categories: backup recovery