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.
Reblogged this on Hungud's Blog.
HI,
I forgot to detach the collection before taking DB backup and now I m not able to restore it. please let me know the procedure to restore the Collection thru the sql DB backup
Is it possible to restore all collections to a new server using the scheduled backup wizard?
I have been trying to follow this but I have become stuck:
http://msdn.microsoft.com/en-us/library/ms404869.aspx#InstallAndConfigureAT
The new server has a newer version of TFS on it so may need to follow your method if I can’t get the scheduled backup wizard to work.
I have installed the exact version of TFS on the new server and verified using your query. It is still throwing the same error though when I try to attach the collection using the TFS console?
thanks. I finally got this working once I had exact versions of TFS & SQL and used SQL Management Studio on the local server not remote.
Reblogged this on James' IT Lab.
Step 3 is what I was missing. Once I stopped and detached, that did the trick. Thanks for this!
Good to hear that it helped, thanks for the feedback, Alex!
Thanks for sharing. Information about step 3 with sql and result for detached collection are very useful! Thanks again.
Same here, Let me know if you got this issue resolved.
TFS 集合没有在控制台分离(系统坏了),只有数据库,能恢复么。
I did not do step #3b
is there any way to fix my database?
I didn’t do step 3 too, is there any solution now?
In our Organization, we are using TFS 2017.1 in Windows Server 2008 with SQL 2014.
We are planning to upgrade to TFS 2018, where minimum requirement of OS is Windows Server 2012.
So, We have got our servers ready with Windows Server 2016 and SQL 2016 installed.
So, Do we need to move our 2017.1 instances first to new Server, then proceed with the Upgrade?
Hello Vinay,
I believe yes, but I have not done that before, so I am not sure.
Does this process keep the changeset history for all files?
I wrote this post 5 years ago, so it might not be true for more recent versions, but yes, this process preserves the file history, because that old version stores everything in SQL.
Pingback: Migrating a TFS project collection to another server | Welcome to My World