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)
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
Advertisements
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: