Moving TFS Databases

One of my clients recently called with a Red Alert/Danger Will Robinson/[Insert high priority phrase here].  Their TFS was down!  The problem appeared to be that the TFS databases were installed on the default C drive which was too small and completely out of space.  They were pretty sure they knew the answer but wanted me to verify it.  For everyone's benefits, I've documented the steps we used to move the TFS-related databases from the C drive to another location.

Note that they were already at a stop-work situation so things couldn't get too much worse.  If you need to do something similar you should approach the process gingerly.  Make sure your backups are good and realize that while you're doing this the TFS and its related services will be unavailable.

1. Stop TFSServerScheduler, SharePoint Timer Service & SQL Server Reporting services..
• Right-click on "My Computer" in the start menu and select Manage.
• Drill into Services and Applications > Services.
• Locate and stop the aforementioned services.
2. Stop the TFS-related Application Pools:  ReportServer, TFS AppPool, TFSWSS, and TFSWSSADMIN.  As an alternative, you may simply shut down IIS altogether if it isn't being used for anything else on the system.  Also note that these application pools
• Still within Computer Management, drill into Services and Applications > Internet Information Services > Application Pools
• Right click on the aforementioned pools and select Stop.
3. Open Microsoft SQL Server Management Studio (Start > All Programs > Microsoft SQL Server 2005 > SQL Server Management Studio) and connect to the Database Engine for your Team Foundation Server.
4. Drill into the Databases node.
5. Locate and Detach the TFS databases.  To do this, right-click on the database and select Tasks > Detach...  Click OK to detach the database.  You can selectively choose the individual databases that you need to move or simply move all of the following:
• ReportServer
• ReportServerTempDB
• STS_Config_TFS
• STS_Content_TFS
• TfsActivityLogging
• TfsBuild
• TfsIntegration
• TfsVersionControl
• TFSWarehouse
• TfsWorkItemTracking
• TfsWorkItemTrackingAttachements
6. 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.  The following database files are candidates based on the same sort order shown in the previous list:
• 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
7. For each database, move the MDF and related LDF file from the source location to your selected destination.
8. Return to Microsoft SQL Server Management Studio, reattach the database files in their new locations.  You can do this as follows:
• Right-click on the Database folder and selecting Attach...
• Click the Add button.
• Browse to the new location and select the first MDF that you've moved and click OK.
• Repeat steps 2 and 3 to select all of the database files that you've 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.
• Close SQL Server Management Studio.
9. Restart the application pools that were shutdown in step 2.  You can use the same steps from step 2 except that you select Start from the pop-up menu instead for Stop.
10. As a good measure, after the application pools are restarted, I restart IIS itself.  This probably isn't necessary but I am paranoid.  If taking IIS down on your TFS server doesn't work in your environment, skip this step.  To restart IIS, right-click on the Internet Information Services node in the computer Management console and select All Tasks > Restart IIS...  If you opted to take IIS down as a whole in step 2, now is the time to restart it.
11. Start the services that were stopped in step 1.  This process is again the same as that given in step 1 except no you are starting services instead of stopping them.
12. Bring up Team Explorer and verify that everything works.  Make sure you check the work items, reports and team portals to make sure everything looks good.
13. Take a peek at the Application and System event logs and make sure that nothing unusual happened during your testing of the new environment.