Get Last Backup Date

There are many ways to go about getting the last backup date for any and all database but I tend to lean towards set based methods over using the GUI for many reasons. One in particular is for the fact that I can turn set based into an actionable report since monitoring backups is important after all.

The method I use the most is the following because it only returns information about databases that currently exists. I really do not need to see information about what used to exist, but I will also show that example as well. Before I go on you might want to understand the anatomy of both the master.sys.databases catalog view and msdb.dbo.backupset table.

/* GET LAST BACKUP DATE FOR ALL EXISTING DATABASES */
SELECT d.name, MAX(b.backup_finish_date) 'Last Backup Date'
FROM master.sys.databases d
	JOIN msdb.dbo.backupset b ON d.name = b.database_name
	WHERE (b.type = 'D' OR b.type = 'I')
GROUP BY d.name
ORDER BY d.name	 

The results indicate (104 row(s) affected). So I have 104 databases on this particular instance.

This is the example of returning the history for the last backup date which may contain information about past databases which depends on your maintenance for backup history. So this may vary.

/* GET LAST BACKUP DATE FROM BACKUPSET */
SELECT database_name, MAX(backup_finish_Date) 'Last Backup Date'
FROM msdb.dbo.backupset
WHERE (type = 'D' OR type = 'I')
GROUP BY database_name
ORDER BY database_name

The results indicate (108 row(s) affected) so between the two statements you can see there is a difference of four rows which is why I avoid using the msdb.dbo.backupset as the only source of record.

The easiest way to determine which databases no longer exists is to execute the following.

/* RETURN NON EXISTING DATABASE NAMES */
SELECT DISTINCT database_name 
FROM msdb.dbo.backupset
WHERE 
	(
		database_name NOT IN
			(
				SELECT name FROM master.sys.databases
			)
	)

The results indicate that (4 row(s) affected) and the names of the database that are not current.

Advertisements

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