Moving System Database mdf and ldf file locations

Question

Moving System Database mdf and ldf file locations

Janice R

SSC Enthusiast

Points: 125
More actions
August 18, 2005 at 7:06 am

#90772

I am doing a new - named instance - install on a new Server and I'm having a hard time getting my brain around how to set up the data and log file locations how I want them. I know how to change the default locations for data and log files once the Instance is up and running, but when I do the install, it puts the mdf and ldf files for all of the System Databases in a folder called mssql$instancename on the drive I am specifying.
What I want to end up with is a folder for each of the database (ie, master, model, msdb, tempdb, userdb1, userdb2, etc...) on two different drives and house the data and log files seperately.
Can I do this at install time or do I need to move the system database files around after install. If so, can someone outline the steps for doing this????

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

For moving them, take a look at my checklist for moving system databases.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

carolina garcia Mr or Mrs. 500 Points: 537 More actions · Answer 2

janice

I am not sure but I think there is nothing you can do at the moment of the installation. System databases are going to be in that folder but after that you can move the location for temp database.

What we do everytime we create a new database is the following

1- We have our backups in a NAS server, so we have to create the folder in that server

exec xp_cmdshell 'md \\Itfectp07\sqlbk$\HQTD01\ITSeTrust'

exec xp_cmdshell 'md \\Itfectp07\sqlbk$\HQTD01\ITSeTrust\DATA'

exec xp_cmdshell 'md \\Itfectp07\sqlbk$\HQTD01\ITSeTrust\LOG'

2- Then we create the folder for that database , of course different drives, but we defined a folder SQLDB for data in drive G and SQLDB for log in drive L

exec xp_cmdshell 'md G:\SQLDB\ITSeTrust\DATA'

exec xp_cmdshell 'md L:\SQLDB\ITSeTrust\LOG'

3- Then we create the database

CREATE DATABASE ITSeTrust

ON

(NAME = 'ITSeTrust_DAT_1'

,FILENAME = 'G:\SQLDB\ITSeTrust\DATA\ITSeTrust_DAT_1.MDF'

,SIZE = 20MB

,FILEGROWTH =5)

LOG ON

(NAME = 'ITSeTrust_LOG_1'

,FILENAME = 'L:\SQLDB\ITSeTrust\LOG\ITSeTrust_LOG_1.LDF'

,SIZE = 5MB)

Does that helps?

Janice R SSC Enthusiast Points: 125 More actions · Answer 3

Yes, thank you! It all helps! and the Checklist for moving system databases is awesome.