TFS - Fixing Work Item Updates made in Excel

One of the cool things about TFS is it's integration with Excel. The integration allows you to link work item data and publish it back to TFS, which is really handy for BA's, Team Leaders and Project Managers.

Unfortunately, with this feature comes the ability to make a huge mistake as well if you are not careful.

Recently, I had to find a way to revert 400 hundred work items which were unintentionally updated by one of the business team. They were using excel, copying and pasting from one TFS linked document into another and accidentally answered "yes" to the publish on the target excel document which resulted in every work items title and description being overridden.

Unfortunately, there was not quick and easy way to revert this so I had to figure out some way of doing it.

What I came up with was rather than trying to rollback the changes by fiddling with the TFS database (which is also a very risky thing to do) I thought that I would just override the mistaken records with the previous data (ironically with Excel - which is the same thing that made the mess!) and leave the invalid data in the workitem, so it would dissappear in the history.

Here's what I did:

1. Queried the "workitemsare" table and the "workitemswere" table to find those records which were accidentally updated (looking at the change date field and the changed by field to find the right time of the incidient and user who publish the records).
2. Found the previous version of the records by looking in the "workitemswere" table by comparing the records found in 1. above to the "workitemswere" table and finding the most recent update that occurent before the incident.
3. From the results, build CSV file that could be opened in excel and be pasted over the top of a TFS linked document containing those records which were affected by the incident. It's important that both the excel documents (both the extract and the linked document) were in the same work item order and had the same columns that needed to be updated.
4. Paste the correct values over the top of the bad records in the linked excel spreadsheet.
5. Publish the correct values back to TFS from excel, resolving any issues where the new "correct" values broke any TFS workitem status state rules.

If you ever need to do this, here's a sample of the scripts which should get you started:

-----

-- Create a tempory table to put the data in
SELECT 1 AS Id,
fld10010 AS Priority,
State,
[Fld10094] ExternalSystemId,
[Assigned To],
Title,
fld10005 As [Resolved Date],
[Changed Date]
INTO #workingtable
FROM workitemsare
WHERE id = null

-- Grab the data that we are after and throw them into a temp table,
-- filtering by user and approximate time.
INSERT INTO #workingtable
(
id,
Priority,
State,
ExternalSystemId,
[Assigned To],
Title,
[Resolved Date],
[Changed Date]
)
SELECT id,
fld10010 AS Priority,
State,
[Fld10094] ExternalSystemId,
[Assigned To],
Title,
fld10005 As [Resolved Date],
[Changed Date]
FROM workitemsare
WHERE [changed by] = '[user name]'
AND [changed date] BETWEEN '2008-08-26 08:41:00'
AND '2008-08-26 08:42:00'
UNION
SELECT id,
fld10010 AS Priority,
State,
[Fld10094] ExternalSystemId,
[Assigned To],
Title,
fld10005 As [Resolved Date],
[Changed Date]
FROM workitemswere
WHERE [changed by] = '[user name]'
AND [changed date] BETWEEN '2008-08-26 08:41:00'
AND '2008-08-26 08:42:00'

-- select the correct records (finding the most recent change before the incident),
-- making sure that the column order and record order match that of excel spread sheet
-- you will use to paste over the top
-- (export this to CSV)
SELECT WIW.id,
ISNULL(CAST(WIW.fld10010 AS VARCHAR(10)), '') AS Priority,
WIW.State,
ISNULL(WIW.[Fld10094], '') AS ExternalSystemId,
WIW.[Assigned To],
WIW.Title,
WIW.fld10005 As [Resolved Date],
WIW.[Changed Date]
FROM #workingtable MWT
INNER JOIN [WorkItemsWere] WIW ON MWT.Id = WIW.Id
WHERE WIW.[Changed Date] = ( SELECT MAX([Changed Date])
FROM [WorkItemsWere] AS WIW2
WHERE WIW.Id = WIW2.Id
AND WIW2.[Changed Date] < '2008-08-26 08:41:00'
)
GROUP BY WIW.id,
WIW.fld10010,
WIW.State,
WIW.[Fld10094],
WIW.[Assigned To],
WIW.Title,
WIW.fld10005,
WIW.[Changed Date]
ORDER BY WIW.Id

----

Notes:

- Some of the fields have column values that aren't well named (auto genereated by TFS), to find where they are, look in the Fields table for the correct field/column mapping.

- WorkItemsWere, WorkItemsAre, Field tables can be found in the "TfsWorkItemTracking" database.

- WorkItemsWere table contains the previous states of each work item

- WorkItemsAre table contains the latest values for each work item

- DO NOT modify the TFS database directly!!!!

Cheers

Matt