Change the path SQL 2008 Server Database

In the most cases there are benefits when all data (log and db) files are on the network storage. Think off the benefit in terms of performance and backup. The Install wizard of Microsoft SQL Server 2008 shows only the option to change the path of the temporary database. In this guide I show you how to move the databases. Please keep in mind to create a backup of your db’s before starting. I know backup is only for hot shower takers, gym bag losers and armpit hair blow-dryers;-). On my test server the path looks like:

master.mdf, masterlog.ldf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

Model.mdf, modellog.ldf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

MSDBdata.mdf, MSDBlog.mdf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

Tempdbv.mdf, templog.ldf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

planetgeek.mdf, planetgeek_log.ldf

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA

First thing you notice the filenames are all tuned in the same schema, pls. Microsoft it cannot be so hard. Our mission is to move all log files in the path D:\mssqlserver08 and all data files to E:\mssqlserver08. The first thing we should do is give the sqlserver service account user read and write rights to this two directories. This step is not quiet necessary but the remote db creation and auto grow features will not work. Let’s start with the master db. Start the SQL Server Configuration Manager. Click “start” -> “run” and type “SQLServerManager10.msc” and right click on the properties from the SQL server Service.


In the advanced tab you have to edit the Startup Parameters


The default value is (keep in mind there are no spaces!!!):

-dC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA\master.mdf;

-eC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\Log\ERRORLOG;

-lC:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER08\MSSQL\DATA\mastlog.ldf

-d is the path to the master.mdf

-e path of the “ERRORLOG” File.

-l is path to the ldf File

After change the path to (keep in mind there are no spaces!!!):




stop the sql server (for cluster use you can use an UNC path and share name. This is useful on relaxed security cluster environments). Copy the master db files to the new path. And start the SQL server Service. One step done four steps are left, so let’s move on with the temporary db. Open the SQL Server Management Studio and open a new query and enter the following lines


After the alter database statement you need to stop the Sql Server move the files in explorer to their new location and start the SQL Server Service. Many of you are maybe wondering why “ … name = tempdev, …” and the “name = templog” in the SQL query. This is the internal database name. A very easy name to get this name is stored procedure sp_help


with this procedure you are able to easily modify the path of all other databases. Normally we would have finished at this point. But after then years of experience as IT guy I know that “developers” often don’t care about path in the file system (developers who write for planetgeek are not this kind of developers 😉 . So we should change the default database creation path to ensure it will work even when we are not in the office (Yes the IT Professionals have Holydays;-).


Enjoy the comfort of non direct attached storage, RIDE ON


About the author



  • Great article but your diagrams have been degraded to unreadability. You need higher quality pictures so that the text is readable or just write out the rest of the commands after step 1. thanks!

  • When I go to the SQL Server Configuration Manager, and I select the SQL Server Services, I am not able to see the SQL Server Service or anything else. It is actually empty.

    My nodes are SQL01 & SQL02, but the actual global (UNC) name is SQLPROD.

    Why I cant see the SQL Server Service? Do I need to stop the cluster process before I try to change the parameters on the SQL Server Service?

  • Hi Jose
    Sorry I am a little short of time in this week, I have to score in some exams at the university next week. But in the Microsoft KB article 953504 you find the way to change the parameters in SQL Server on a Windows Cluster. I guess there is now way to do it whiteout taking the cluster offline.

    regards Konrad

  • use master
    Alter database tempdb modify file
    (name = tempdev, filename = ‘D:\SQLDATA\tempdb.mdf’)
    Alter database tempdb modify file
    (name = templog, filename = ‘D:\SQLDATA\templog.ldf’)

By konrad.dambeck

Recent Posts