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!!!):

-dD:\mssqlserver08\master.mdf;

-eD:\errorlog\ERRORLOG;

-lE:\mssqlserver08\mastlog.ldf

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

Konrad