Rebuilding your Team Foundation Server

Before you do anything, make sure you make a note of what your reporting services encryption key password is. Otherwise you will have all sorts of headaches when trying to restore your TFS reporting databases (following the procedure).

Anyway... You CAN restore to the same server, as long as the MAC and IP stay the same. I can personally testify that it works. It is very good to know if you have to rebuild your Team Foundation Server for any reason. There are some catches to look out for that I discovered along the way though.

If you keep getting errors such as this:
Source: TFS Version Control
SqlException Details:
Exception Message: Cannot resolve collation conflict for column 5 in SELECT statement.
SQL Exception Procedure: prc_QueryConflicts
etc..

...then you have done what we did - use a different collation for the new install of SQL. Why did it happen for us? Our initial TFS was built on Windows Server 2003 regional settings of Australia. After we rebuilt it the region was not set (as part of the Windows Server 2003 setup), which means it defaults to US. Therefore when we followed the install guide for TFS and left the default collation for SQL, we infact selected a different collation to the original. So, we got the collation errors. I imagine that the stored procs must depend on the default server collation (as opposed to default database collation) since even though the database collation was still correct, it still didn't work.

We detached all the databases again (ie additionally to the reinstall) and changed the default server collation back to the "Australian" collation (ie the one that all the Tfs databases used from the original installation).

This also fixed another problem that was caused by the culture mismatch:
Source: TFS Warehouse
TF53010
Cube processing runtime error:
\r\nMicrosoft.AnalysisServices.OperationException: The member
'[3/29/2006]' was not found in the cube when the string,
[Date].[Date].[Date].[3/29/2006], was parsed.
The END SCOPE statement does not match the opening SCOPE statement.
MdxScript(Team System) (66, 1)
etc...

Login failed for user 'DOMAIN\tfsservice' - after reinstalling SQL Server (to change the default server collation) you need to add the
tfsservice user to Security/Logins for the server instance (ie not the database, it should still be there from before).

When we added the users we also set the default language to British English (as opposed to English - "which is US English").

I am very relieved to say that everything is now working correctly with no errors in the event log. All of our documents still work properly too, which is why we didn't want to change the server name (especially since I put in HEAPS of time on the project plan and didn't want to lose it).