How to repair or re-upgrade only TfsBuild database in TFS 2008SP1?

Question

 

Recently with a lots of troubles I upgraded TFS 2005 to TFS 2008. The latest issue that I hacked was missing columns in TFSBuild database.

 

We mostly are using Source Control and Work Items functionality of TFs, antd they work fine after conversion for a few weeks.

 

But now when we tried to use builds, we've got the errors(see below).

Is it possible to repair or re-upgrade  only TfsBuild database in TFS 2008SP1  without affecting source control functionality?

All SPs in TfsBuild are encrypted, that make it harder to investigate/workaround. 

Description of the error:
TF53010: The following error has occurred in a Team Foundation component or extension:
Date (UTC): 7/10/2008 4:08:35 AM
Machine: Application Domain: /LM/W3SVC/904242201/Root/Build-3-128677648568436123
Assembly: Microsoft.TeamFoundation.Common, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a; v2.0.50727
Process Details:
  Process Name: w3wp
  Process Id: 5560
  Thread Id: 5716
  Account name:

Detailed Message: TF53014: A crash report for this problem was recently created; a new crash report for this occurrence of the problem is not being created. This problem has occurred 3 times.
Web Request Details
    Url: http://MyServer:8080/Build/v2.0/BuildService.asmx [method: POST]
    User Agent: Team Foundation (devenv.exe, 9.0.30729.1)
    Headers: Content-Length=498&Content-Type=application%2fsoap%2bxml%3b+charset%3dutf-8&Accept-Encoding=gzip&Accept-Language=en-AU&Expect=100-continue&Host=MyServer%3a8080&User-Agent=Team+Foundation+(devenv.exe%2c+9.0.30729.1)&X-TFS-Version=1.0.0.0&X-TFS-Session=df97fa8d-07a5-49a6-8b8c-9d23a875b9b4&TF-Instance=df97fa8d-07a5-49a6-8b8c-9d23a875b9b4
    Path: /Build/v2.0/BuildService.asmx
    Local Request: True
    Host Address: 172.28.6.139
    User: Build [authentication type: NTLM]

Exception Message: Invalid column name 'Reason'.
.TfsBuild..prc_QueueBuild: Database Update Failure - Error %error="207";% executing INSERT statement for @queuedBuilds (type SqlException)
SQL Exception Class: 16
SQL Exception Number: 207
SQL Exception Procedure: prc_iiQueueBuilds
SQL Exception Line Number: 239
SQL Exception Server: MYSERVER
SQL Exception State: 1
SQL Error(s):
SQL Error[1]: System.Data.SqlClient.SqlError: MYSERVER.TfsBuild..prc_QueueBuild: Database Update Failure - Error %error="207";% executing INSERT statement for @queuedBuilds
    Class: 16
    Number: 900004
    Source: .Net SqlClient Data Provider
    State: 1
    Procedure: prc_QueueBuild
    Line Number: 72

Exception Data Dictionary:
HelpLink.ProdName = Microsoft SQL Server
HelpLink.ProdVer = 09.00.3073
HelpLink.EvtSrc=MSSQLServer
HelpLink.EvtID = 207
HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink
HelpLink.LinkId = 20476

Exception Stack Trace:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.TeamFoundation.Server.SqlResourceComponent.execute(ExecuteType executeType, CommandBehavior behavior)

 

---

Michael Freidgeim, http://geekswithblogs.net/mnf/

Answer 1

Hi Michael
  

There's no way to just repair the TfsBuild database.  We might be able to rely on the reparation of TFS. And repair TFS from the installation media will repair all databases. Back up database is a must before you take further actions. And this article describes how to back up TFS.

---

Please mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

:) You can test the forum in MSDN Sandboxx http://social.msdn.microsoft.com/Forums/en-US/msdnsandbox/threads.

---

Please mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

I followed Buck Hodges  suggestion to " drop the columns that you hacked in and run repair". It fixed the problem.

---

Michael Freidgeim, http://geekswithblogs.net/mnf/