Don’t change a TFS database by hand; How I got myself in real trouble

Last week we changed some of our work item types after a review session we did for our project. We discovered from our work item database and feedback from our team that the types had to many states and transitions that made working with those types tedious. We changed the workflow according to a new definition we created and tested this in a different Team Project we have set up especially for test purposes of these kinds of changes.

 

When we tested the flows one thing we overlooked was that the work item type name was not accurate anymore. We had a type called “Change Request” and now we had a new type called “Change”. For some reason we did not spotted that change of name when working in the test project and we gave a Go to change the type definition for our production TFS as well. After a day or two I discovered people where now using “Change” and “Change Request” and that was something we obviously did not want. So I thought I would just remove the wrong work item type. Unfortunately I discovered that this was one of the features that have been cut for V1 L

 

So what to do? Well the only supported option you have is to change the flow of the wrong work item type and make the state transition from new to any other state impossible. This would prevent the team of creating those type of work items. The way to do this is to add the following transition to your flow:

 

<TRANSITION from=”” to=”Active” not=” “[global]Team Foundation Valid Users”>

&nbsp;

I felt this was not something that would make people in our team happy, and that is where I did something that I will recommend you to never do. I thought to myself, how hard can it be to hack the WIT database and remove the type by hand? At first glance at the database this seems very feasible. You see columns called fDeleted in many tables and that makes you think you can change this by hand very easily. So I did this silly thing of updating those tables setting the fDeleted field and thought I would be ok. I want to warn you THIS IS NOT SO EASY! I ended up with a corrupt WIT database and there was no other way than restoring my WIT database. At that moment I discovered something terrible, the backup had not run for a few days because the device we dump the db to was full. ARGH, L

&nbsp;

I have been very fortunately that I had a Microsoft engineer willing to help me out and we spend many hours to get the WIT database back to normal. This was only possible because I did not delete any data. If I had deleted some records, I would really be Toast and would have to go back several days.

&nbsp;

So what did I learn from this and what would I like you to learn from that as well?

• Always make sure you test work items properly before placing them on your production server. This includes the name of the WIT Type!


• Never, I mean Never go and try to make manual changes to the WIT database, it is way more complex then you can imagine and you will end up with a corrupt database


• Always make sure you have your databases backed up properly and have this verified!


• Don&#8217;t be stupid enough to think you know it all and lose any form of professionalism when making changes to a production database. When you do make changes to any production database make a backup before you start hacking.


• We realy need the option in TFS for deletig a Work item type we don’t want to use anymore.

After this escapade I thought to myself how I could be so stupid to do this kind of thing? I did not come up with an answer why I did it, but to make it up to myself I thought I would at least share this with others so they might learn from this as well.