Occasionally you may want to move your Team Foundation Server database to another server, because for example:
- You want to upgrade the underlying hardware or software infrastructure.
- You want to split the project collection between multiple servers.
- You want a copy of your live data in your test environment.
The process is fairly simple:
- Ensure that the target server has the same or newer SQL Server version than the source environment. It is important, because you cannot restore a SQL backup which was created with a newer SQL Server.
- Ensure that you have exactly the same version of TFS in both environments. Not only service packs, but also minor hotfixes matter!
- In your source server:
- Click Stop Collection in the TFS Admin Console.
- Click Detach Collection in the TFS Admin Console.
- Start SQL Server Management Studio and create a full backup of the database of the project collection.
- Copy the SQL backup to your target SQL Server.
- In your target server:
- Use SQL Server Management Studio to restore the database backup to a new database.
- Use Attach Collection in the TFS Admin Console.
- Update the SharePoint and Report Server settings according to your needs.
It may happen that when you try to attach the project collection TFS cannot find the restored database and you receive the following error message:
TF254078: No attachable databases were found on the following instance of SQL Server: MyServer. Verify that both the name of the server and the name of the instance are correct and that the database was properly detached using the detach command in the Team Foundation Administration Console.
The error message is really correct, so you can check the following:
- Verify that you can connect to the SQL Server instance and the database in it with the TFS service account.
- Verify that you have exactly the same TFS version in both environments.
- Verify that you have not skipped Step 3b and correctly detached the project collection from TFS.
TFS verifies the second and the third criteria by querying the list of databases in the SQL Server and then executing the following query in each of them:
SELECT name, value FROM sys.extended_properties WHERE name LIKE 'TFS_%'
This query returns the custom properties of the databases which start with “TFS_”. You can do the same in your target environment, and you will get something similar for your Configuration database:
This is for an attached database:
And finally you will get something like this for a correctly detached database:
If you cannot see the TFS_SNAPSHOT_STATE property with the Complete value, than you have a fair good chance that you forgot to detach the project collection in the TFS Admin Console before created the SQL backup.