Introduction
Since its inception, to date, Microsoft has failed to provide a satisfactory mechanism to move WorkItems from one TFS Project to another.
In mid 2010, Lang Hsieh posted this approach and rational to moving TFS WorkItems.
Independently, in mid 2015, having similar needs to move a large number of TFS WorkItems without losing the history, assignments, links and other associations, and being unaware of Lang Hsieh's work, I performed similar research, resulting in this CodeProject article and a convenience utility to reduce human error.
For TFS 2017, Microsoft has changed the name of their WorkItem* tables as well as the format and function of several columns. They also now scrub the WorkItem tables and remove any incompatible entries. Thus this approach no longer works for TFS 2017. If I find an approach I will provide an update.
See the comments below where TFS Program Managers advise against the use of this approach, with the 'loss of support' statement from Microsoft being verified earlier by another CodeProject contributor. The most recent comment requests that this article be deleted from CodeProject. Though I take their objections seriously, the best resolution would be for Microsoft to provide adequate native support for the movement of WorkItems. I need to manage my team and their project communications and assignments with cohesion and fluidity. Thus I esteem the future upgrade headache, with the fallback of a migration less of an issue than the team disruptions caused by the loss of WorkItem associations.
I depend upon this roguish ability to move TFS WorkItems between TFS Projects to manage my team of product developers without the loss of change history or attachments. But I do not have a support contract to lose and do not plan to upgrade soon. And other than the need to modify the state or assignment fields, from the TFS UI, when the state or assignment is not a member of the new project's pool, I do not experience any harmful operational effects.
Obviously, this approach is not for everyone. But if you also have this need, and can't wait for the TFS team to complete their implementation, ... start with the Caveats and read on.
Caveats
Use at your own risk. In the comments below, Microsoft Program Managers for TFS strongly object to this approach and claim serious consequences. (Note: Maintaining a backup of your database files and TFS servers is advisable for much more than any side-effects from moving WorkItems.)
This approach will not work for TFS 2017, because of a maintenance pass that will remove items that have been moved by this approach.
The Crux of the Approach
The TFS database is highly complex as it allows for custom fields and future defined workflow to be accomplished. There are several views in the database that support the myriad of joins and filters needed to stitch together a work item from various database tables. At the heart of the work item schema are several base work item tables, with duplicate data, that manage the state transitions and caching of work items. These base tables have no direct reference to the project that the work item is contained in. Project containment is derived from both the Area record as well as the Iteration record which do, each, have an indirect record association to the project.
The query below highlights what is needed to void your TFS Support Contract and move a work item to another project. In short, change both the AreaID and the IterationID to an AreaID and IterationID that belong to the target project. These are existing and normalized values.
For TFS 2013:
UPDATE [Tfs_xxx].[dbo].[WorkItemsAre] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[WorkItemsLatest] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[WorkItemsWere] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
For TFS 2017, in addition to changing the table names and number, Microsoft shifted from AreaId to AreaPath and from IterationId to IterationPath for the above associations. Additionally, the AreaPath and IterationPath columns changed from human readable VARCHAR to machine readable VARBINARY. The problem is that a maintenance pass can tell that these moved records do not belong and the server will flag these moved records and delete them.
UPDATE [Tfs_xxx].[dbo].[tbl_WorkItemCoreLatest] _
SET AreaPath=@AreaPath, IterationPath=@IterationPath WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[tbl_WorkItemCoreWere] _
SET AreaPath=@AreaPath, IterationPath=@IterationPath WHERE ID=@WorkItemID
The xxx in Tfs_xxx should be replaced with the correct project collection database for your installation, which can be found from SQL Manager or deduced from the Team Foundation Administrative Console.
Using the TFSMove Utility (compatible with TFS 2013)
I created the TFSMove utility to increase convenience and decrease the human error of hand entered SQL in SQL Manager. Above are links to download the executable files for the utility as well as a slightly simplified version of the source for the utility.
Entering the URL to your TFS server fills the Project ComboBox with the projects to select from.
Example:
https://<server>:<port>/tfs
Enter a SQL Connection String to the SQL Server hosting the TFS database.
Example:
Server=<server domain name>;Database=Tfs_xxx;Trusted_Connection=yes;
You will need to specify and authenticate to the TFS database, e.g., Tfs_xxx, where xxx refers to your TFS Project Collection.
If you do not have Windows Integrated Access working, you can create a database user specifically for this access and provide user id and password credentials in the connection string. Otherwise, you have to specify Trusted_Connection=yes;.
If needed, get help with your connection string and your database access rights from your Database Administrator.
Use the ¡Test! button to ensure that the connectivity is functional. If it works, it will tell you the number of work items in your project collections. Otherwise, it will display the SQL Server connectivity error that caused it to fail.
In the Query: field, you can either enter a work item number (e.g. 17982) or a TFS WorkItemQuery. The WorkItemQuery is a limited SQL language that is used by the TFS web interface for work item queries (see this link). If you do bulk moves, it is helpful to use the [Area Path] condition in the SQL WHERE clause so that the target Area is a better match to the replacement Area of the destination project.
Example:
SELECT * FROM WorkItems WHERE [Area Path]='<ProjectName>\<AreaName>'
The Search button will fill the results pane with a detail of a single work item or an Id and Title list of multiple work items resulting from the Work Item Query.
Select a target Project, target Area, and a target Iteration for the work item(s). Once these and all of the above conditions are met, the ¡Move! button will be enabled. Pressing the ¡Move! button will execute the parametrized form of the SQL referenced above to move the work item(s) to the selected project.
Example:
For TFS 2013 Only:
UPDATE [Tfs_xxx].[dbo].[WorkItemsAre] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[WorkItemsLatest] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
UPDATE [Tfs_xxx].[dbo].[WorkItemsWere] _
SET AreaID=@AreaID,IterationID=@IterationID WHERE ID=@WorkItemID
History
