Home > backup recovery > Get Last Backup Date

Get Last Backup Date

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
  1. August 10, 2011 at 5:52 pm

    I like this script

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: