Migrate Team Foundation Server’s SQL Server to another SQL Server (same or new version)

By | February 14, 2013

Yesterday we migrated SharePoint’s SQL Server to a new instance today we will be migrating Team Foundation Server (TFS) SQL database to new SQL instance.
In this example we are migrating it to a new version of SQL Server which is from SQL 2008 R2 to SQL 2012, this will also work on the same SQL version.

What we had done here is a very simple step by step instructions and this is what I’ve done with our live environment. I only migrated the Project Collection and Configuration Database so if you have the same scenario you can follow this with care of course. So let’s start.


Moving TFS Project Collections

1. Detach Project Collection

1 Detach Project Collection

Go to Team Foundation Server Administration Console -> Application Tier -> Team Project Collections then choose the collection you want to migrate. Once chosen choose detach then click next on each following step to confirm.

Once finished you will see a Window similar to this, click Complete then close.

1b Detach Project Collection

2. Detach you TFS_Configuration Database in SQL Server

2 Detach SQL Server Instance

3. Copy the Database Related Files

Copy all database related files (.mdf, .ldf) to the New SQL Server

3 Copy Detached Database Files to New SQL Server

4. Attach the Database in the new SQL Server

4 Attach Database

5. Attach Project Collection

Go to Team Foundation Server Administration Console -> Application Tier -> Team Project Collections then choose Attach Collection.

5 Attach Project Collection

5b Attach Project Collection

Once finished you will see a Window similar to this, click Complete then close.

5c Attach Project Collection

At this stage try doing a check in and check out to see if everthing worked, compare file history as well to double check

Moving TFS Configuration Database

1. Stop IIS on TFS Server

Using the Admin context execute “iisreset /stop” at the command prompt.

If you can’t stop IIS there is another alternative. Just stop the TFS related sites and application pool in the Server

Stop TFS Website on Server

1 Stop TFS Website

Stop Application Pools, those two highlighted in the screenshot below

2 Stop TFS Application Pools

2. Stop TFS Job Agent service

You can stop this via services.msc or execute “net stop TfsJobAgent” at the command prompt.

3 Stop TFS Services

3. Detach the Tfs_Configuration database

Do it similarly with the step 2 on migrating the project collection

4. Attach the Tfs_Configuration to the new SQL Server instance

5. Prepare new SQL for TFS instance

Using Admin context open again Command Prompt if you had closed it then navigate to “C:Program FilesMicrosoft Team Foundation Server 2010Tools”
Execute “TfsConfig prepSql /sqlInstance:{your_new_server_name_here}”

3 Prepare SQL Server for TFS

6. Register new SQL for TFS instance

In the same command prompt instance Execute “TfsConfig registerDb /sqlInstance:{your_new_server_name_here} /databaseName:Tfs_Configuration”

4 Register SQL Server instance for TFS

7. Reapply Service Account

Go to Team Foundation Server Administration Console -> Application Tier then click on Reapply Account.
Select the account you use then click OK

5 Reapply Account

If successful you will see something like this

5 Reapply Account Message

8. Start the TFS Job Agent service

Start the TFS Job Agent service via services.msc or execute “net start TfsJobAgent” at the command prompt.

9. Start IIS on TFS Server

Using the Admin context execute “iisreset /start” at the command prompt.
If earlier you just stopped the TFS related sites and application pool just run it all back again.

You have now successfully migrated your database.


2 thoughts on “Migrate Team Foundation Server’s SQL Server to another SQL Server (same or new version)

  1. dcs

    Hi Raymund,
    Thanks for the info on this process, when I get to step 6 (registerdb) I get the following message when running this command:
    “The following exception was caught while trying to validate the database:
    TF30045: The instance information has not been configured or is not available fo
    r this Team Foundation Server. Please contact your Team Foundation Server admini
    strator.”
    Any assistance would be appreciated.

    Reply
  2. Antonio

    In TFS 2012, tfsConfig doesn’t have the prepSQL command. What would be the equivalent for it? Is it prepareClone?

    Reply

Leave a Reply