Summary of additional experiences with TFS database separation

Because the TFS database already has more than 100 g, the server D disk on which it is located has no space to meet the growth rate of the TFS database, so it must be detached and replicated to other disks. In the separation process, successively separated the ReportServer, reportservertempdb, TFS_Warehouse, TFSWarehouse, WSS_Config, wss_content database.
In the separation of the TFS_Configuration database, and then want to separate tfs_defaultcollection this main library, the "database can not log in" error, then I was ignorant, and so the reaction came to mind, is this the default login data for TFS, Quickly open the TFS console discover the connection string for the TFS database tier as follows:

Data source=tfs-server;initial catalog=tfs_configuration;integrated security=true

Sure enough, the default database is TFS_Configuration. Now regret also no way, who let just woke up overtime, brain is paste. Then it was quick to think of various ways to make the following attempt:& R (P9 c3 O2 x& Z
1. Use the TFSConfig command: This command is used to manage the server configuration.

In the process of using this command, have tried the recover, repair, Registerdb, PrepSQL and other commands, in the course of execution, the "TFS cannot connect to the database library" error. This command has not been used before, and it appears that the TFSConfig command was executed with the correct tfs_configuration database connection, and the server configuration was made by changing the database information.
2. Using SQL scripts

But not a few attempts, even the database is logged in, the script can not be executed.

3 SOS Database Expert
means that TFS is not understood, and the SQL database is not logged in and cannot be manipulated.

Finally I collapsed, do not want to re-install TFS, this workload is not generally big Ah, the weekend was already tired with the dog, and then all night overtime, Eva how to do AH. No way, can only take a rest, and my home and South play for a while to continue to try the solution. Looking at the login options for SSMs, click below:

This connection parameter has not been used before, but look at the following "Note: Connection string parameters will overwrite the graphic selection on other panels", and suddenly, I will try to change the default database of the connection string to master (System database). I then fill in the following connection string into the text box as follows:
Data source=tfs-server;initial catalog=master;integrated Security=true

Click Connect, as you want to enter the database, and TFS all the database can see, it is 1 tfs_configuration database. Then let it naturally click on the interface "attach the database", but the unfortunate thing happened again, reported "unable to connect to the default database, account cannot log in" error. Again, this cannot be attached, TFS can not use Ah, or only re-installed AH. And then began to solve how to attach the database, since the interface does not work, then this time login through the script should be OK, try the following additional commands, Amitabha, add success, client access to Tfs,ok, as before.

• EXEC sp_attach_db @dbname = N ' dbname ',
• @filename1 = N ' database path,
• @filename2 = N ' log path;

Excitement no longer reload, back to the server, want to go through the SA TFS database to continue the last main library interface separation operation, the result is still unable to log on to the TFS database, click All the right menu will be error, really cue unravelling ah, how the problem one after another, then continue to solve it. Since SA does not work, perhaps because its default database is also tfs_configuration, how can I change the default database of the SA or through scripting? But set the default database do not dare to try Ah, before the use of this command, how to do? What about adding another system administrator? You can set the default database for SA by logging in with another administrator account, which is sure to be a reliable operation. Finally execute the following script: EXEC sp_addsrvrolemember n ' Test ', ' sysadmin ') U-^% F! }4 F
Copy the code to the current SQL in the existing 1 test accounts to the Administrator role, and finally log on by test, and then in "Security" set SA and Windows Administrator default database for TFS_Configuration, test all operations, Completely normal, finally solved all the problems.
The whole process is really anxious Ah, afraid in case not, the whole weekend will be finished, and if you re-install TFS, restore more than 100 g of data will certainly encounter various problems, affecting everyone's work. I am now the most fortunate decision is: The problem can certainly be solved, not the last resort, must not be re-installed tfs!  

Summary of additional experiences with TFS database separation