Changing what drive TFS databases are on
I got a question recently about how to change what drive the TFS SQL databases are installed on. It seemed like a question many people might want the answer to, so I decided to post about it.
The first thing the poster noticed was that they changed the default database location in SQL Server Management Studio and it had no effect on where the TFS databases were created when he installed TFS. This is because that setting is a UI setting for SQL Server Management Studio only and does not affect where databases are programatically created (using the CREATE DATABASE TSQL statement).
The recommended way to do this is to let TFS create the databases on the default drive and then go back and move them after the fact.
a. Install TFS
b. Stop the AT - you can use iireset /stop
c. Detach all TFS databases (and log files) - Using the Object Explorer in SQL Management Studio, right click on the database and choose Tasks -> Detach
d. Copy them to a new location
e. Attach the databases - Using the Object Explorer in SQL Management Studio, right click on "Databases" and choose Attach.
f. Restart the AT - you can use iisreset /start
If you absolutely must change the drive that the TFS databases are initially created on, you can do it but it is not for the feint of heart:
You can do it by relocating the model system database as it is used as the template on database creation. The steps for that would be something like the following. I'd recommend you consult your SQL Server documentation if you really plan on attempting this.
a. Run
- alter database model
- modify file (name=modeldev, filename=’<new path>\model.mdf')
b. Restart SQL Server
c. Run TFS setup – databases will be created in @newpath.
Keep in mind that a change to model, will affect all future database creations.
Brian
