Moving TFS Databases

Recently we had to move a complete Team System Foundation database environment from one storage to another; Not much of a surprise since the databases are growing all the time, and the original hard drive had about 20gb.

So we connected a LUN to the TFS server and I had to move the database files. Here's how you do it:

Before You Start

• It's highly recommended that you backup all of your databases.
• The TFS services will not be available (obviously) so you should tell everyone to stop working until you're done even though it won’t hurt the process if they don’t.
• Names of services and other stuff may appear differently on your machine, so be creative when finding the proper services and application pools.

Step 1: Stopping Services and Application Pools

Go to the Services Console and stop the following services:

• TFSServerScheduler
• SharePoint Timer Service
• SQL Server Reporting services

To get there, open the Run Command window (WinKey-R), type “Services.msc” and hit Enter.

Stop the TFS-related Application Pools:

• ReportServer
• TFS AppPool
• TFSWSS
• TFSWSSADMIN.

As an alternative to stopping the application pools, you may simply shut down IIS altogether if it isn’t being used for anything else on the system.

To get there, go to Computer Management, then Services and Applications > Internet Information Services > Application Pools.

Step 2: Detaching the Databases

Open Microsoft SQL Server Management Studio and connect to the Database Engine for your Team Foundation Server.

To get there, go to Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio.

Under the Databases node, locate and Detach the TFS databases.

To do this, right-click on the database and select Tasks > Detach. After making sure there are no active connections to the database, click OK to detach it.

This is the common database list needed to be detached. If this SQL Server instance is only used for TFS, just detach everything.

• ReportServer
• ReportServerTempDB
• STS_Config_TFS
• STS_Content_TFS
• TfsActivityLogging
• TfsBuild
• TfsIntegration
• TfsVersionControl
• TFSWarehouse
• TfsWorkItemTracking
• TfsWorkItemTrackingAttachements

Step 3: Moving the Databases

Locate the database data files and transaction logs that were detached.

By default they are in the following directory: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

Continuing the example database list above, these files are probably the ones we need to move to the new location:

• ReportServer.mdf and ReportServer_log.LDF
• ReportServerTempDB.mdf and ReportServerTempDB_log.LDF
• STS_Config_TFS.mdf and STS_Config_TFS_log.LDF
• STS_Content_TFS.mdf and STS_Content_TFS_log.LDF
• TfsActivityLogging.mdf and TfsActivityLogging_log.LDF
• TfsBuild.mdf and TfsBuild_log.LDF
• TfsIntegration.mdf and TfsIntegration_log.LDF
• TfsVersionControl.mdf and TfsVersionControl_log.LDF
• TFSWarehouse.mdf and TFSWarehouse_log.LDF
• TfsWorkItemTracking.mdf and TfsWorkItemTracking_log.LDF
• TfsWorkItemTrackingAttachments.mdf and TfsWorkItemTrackingAttachments_log.LDF

Step 4: Re-Attaching the Databases

Return to Microsoft SQL Server Management Studio and reattach the database files in their new locations. How?

• Right-click on the Databases folder and select Attach.
• Click the Add button.
• Browse to the new location and select the first MDF that you’ve moved and click OK.
• Repeat the last two steps for all of the database files that you moved.
• Once all of your databases are selected, click OK in the Attach Databases dialog.

Verify that all of the original databases shown in step 5 above are displayed under the Databases folder in SQL Server Management Studio, and close SQL Server Management Studio when you’re done.

Step 5: Restarting Services and Application Pools

Restart the application pools that were shutdown in step 1. Simply go through the same procedure, only start the services instead of stopping them. Restarting the IIS afterwards might be a good idea, too.

To do this, open the Run Command window (WinKey-R), type ‘iisreset’ and hit Enter.

Then start the services that were shut down in the same step.

Success

You’re done. Bring up Team Explorer and verify that everything works. Make sure everything looks good.

It’s also recommended to take a peek at the Application and System event logs and make sure that nothing unusual happened during your testing of the new environment.