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.

Duplicate Commands within the Data Menu of BIDS 2008

I recently stumbled upon a problem where there were duplicate commands within the sub menu of Data in Business Intelligence Development Studio 2008. To give you a birds eye view below is what we were facing.

With a little research and some musical chairs with keywords I stumbled upon the following post: Visual Studio team System 2008 Database Edition FAQ which pinpointed the solution. You’ll need to scroll down a bit till you see “I have installed the GDR, but now I see duplicate commands on the menus in the IDE.”

Resolution Steps

1) Be sure to exit out of all the visual studio sessions
2) Open a command prompt
3) Within the command prompt navigate to %ProgramFiles%\Microsoft Visual Studio 9.0\DBPro\

Execute the following:

DBProRepair.exe RemoveDBPro2008

You will not see a confirmation message after running the above statement. To verify launch BIDS and click on Data. It should now look like the following.

Flight Recorder… WTH?

When I first encountered this I thought to myself… must be a database or service or some sort, not being entirely sure of what to expect. I searched throughout the instance starting with the database engine and moved to the SSAS instance and was unable to find anything remotely named “flight recorder”. At this point I thought this error must have risen from an external call looking for a resource that no longer exists. Little did I realize that it is actually the SSAS log. It’s also been around sin SQL Server 2005.

If you need to determine if it is enabled then this is how you go about it.

Using SSMS connect Analysis Services for that instance. Once connected right-click on the instance and select “Properties” and in the name column just about the 13th row down you will see “Log \ Flight Recorder \ Enabled”