I’ve built a couple of custom reports for a client, and in doing so I’ve been forced to dive deep into the TFS 2010 database, which sometimes leads to interesting findings.

This time I found a strange approach to Areas and Iterations. For all work items areas and iterations are stored in the table xxTree. But then there are two other tables named tbl_Areas and tbl_Iterations, and they also contain all areas and iterations. But without all the other stuff found in xxTree. Now, one might be tempted to believe that these tables are somehow connected and perhaps contain a foreign key pointing to the  xxTree table. But no… As far as I can tell, xxTree is for work items while tbl_Areas and tbl_Iterations is used by MTM2010 for mapping test plans to areas and iterations.

So for anyone  writing queries using these tables, make sure you use the right ones or you will end up with some very strange results.

Brian Harry recently blogged about a cool new feature in the next version of TFS Power Tools (no date yet). Backing up and Restoring your TFS Server is the most exhaustive post and makes for some interesting readin while Backing up your TFS Server with Sharepoint and Reporting more or less just states that you can backup the Sharepoint and Reporting parts of TFS as well.

Now, lets hope they add a tool for restoring the backups as well

I recently stumbled across a interesting case using TFS 2010 where you could end up unintentionally setting a field on a work item that is not supposed to use the field at all. This is probably best explaned with a simple example.

Lets say you team project is based on the MSF for Agile v5 template. You want to modify the Task WIT so that the LinkControl in the Links tab includes a column for severity. This way, whenever a Task is linked to a Bug you will know the severity of the bug without opening it up. Perfect! Lets do it!

I’ll skip the details of how to do this, the interesting part is that even though Task does not use the field Severity you will need to add it to the Task WIT in order to show it as a column in the LinkContol. Name, Field type and Ref name must be identical to what is used in the Bug WIT. And this is where you might make a small but potentially very annoying mistake. If you also copy the rules of this field from the Bug WIT (easily done if you copy/paste between XML-files or if you are just very thorough and decide to specify the field exactly as it is on the Bug WIT) all your Tasks will from now on be asigned the severity level “3 – medium”.

And why is this bad? Well, you’ll probably not notice it for some time. If you are lucky you wont ever notice it and if so, good for you! But chances are that sooner or later someone will get a very wierd result from a work item query or report. Lets say you have a report that counts the number of bugs of each severity level. Depending on the laziness of the person who designed the report, it might not check the work item type because only bugs used to have a severity level… and now this report is telling you that the number of bugs of severity level “3 – medium” is increasing at an alarming rate!

Good news is this that the mistake is fairly easy to solve. Remove all rules for the severity field on the Task WIT, create a query to return all tasks with severity “3 – medium”, export to excel, remove severity value from all tasks and publish the changes back to TFS. Done! Unless your report keeps track of history as well…