Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change SQL Data Root Directory


Change SQL Data Root Directory

Author
Message
lmacdonald
lmacdonald
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 513
I am hoping there is a way to change the "data root directory" after a SQL 2008R2 install. I gave someone the wrong drive information during the install and they have already completed a bunch of servers and installed service packs.
It will take them the entire day to re-install what they have setup so far, so I'm trying to offer some help.

I would like to know how critical this mistake is.

Will losing the drive that we have setup as the data root directory make SQL Server not function anymore?

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/98b97adb-80e9-42e2-8099-cc17dd985b9f/how-to-change-default-database-root-directory-

I found the link above, has anyone tried this to see if it works? Is there a way to re install without re installing service packs? Hope someone can throw me some idea's as a scour the internet. Thanks....
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
If you're just talking about where the actual data files are stored, this shouldn't be an issue. You can change SQL default data file location via the SSMS GUI.

If you're talking about where the actual binaries are installed, that's different matter...

Can you please confirm?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
lmacdonald
lmacdonald
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 513
I attached a screenshot of where the wrong drive information was put during the install.

The result of this incorrect setup has put the following folders on the F: drive. It has created a folder in F:\MSSQL\Data\MSSQL10_50.MSSQLSERVER\MSSQL\
and in this folder are Backup, Data, Jobs, Log and repldata. I do see a Binn folder on the c:\ drive, is that the binary folder?

The majority of folders are empty, except for the log folder so that does ease my concern some. However can
these folders be moved to the C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ location?

The loss of this drive and therefore these folders, will that stop sql from running?
Attachments
install.jpg (52 views, 16.00 KB)
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8826 Visits: 16562
yes the loss of that drive\folder will stop sql server from running, the system database path is where the master database sits and is critical for sql server operation.

You can move the files quite easily. The master database and the errorlog location are moved by amending the sql server service startup parameters, the remainder of the databases will be moved from within the sql server system itself using the T-SQL command


ALTER DATABASE MODIFY FILE



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
lmacdonald
lmacdonald
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 513
We do plan on moving the system database and the msdb database to the C: drive. After that though, is there anything else critical on that drive?
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 7365
As I'm sure most people would tend to agree: why would you place your system files on C:\? In the event of issues from the OS-level or the drive starts to run out of space, your SQL installation might be in jeopardy if you have the binaries/system databases on the same drive as your OS...

Do you have the option of have your IT department map a new drive (say from an external SAN or other attached storage device)? If you do, then they can copy the drive contents from what's current F:\, rename the new drive, and SQL would never know the difference.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
lmacdonald
lmacdonald
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 513
Sorry, I meant to say Master database, not system. It was requested that it be on C:\ along with msdb because it's the most reliable drive and the worst database to lose would be the master. Tempdb will be on a separate drive increase it goes through a sudden growth spurt. There is only two drives on the systems to work with. We won't allow the C:\ drive to run out of space, nothing else will be installed on it. The server host SQL only, all data files that grow frequently will be on another drive, plus we monitor it tightly.
lmacdonald
lmacdonald
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 513
I was able to move the master database and start SQL. I wanted to move msdb as well so I did the alter command for that and restarted. However now i have a big problem, I can't look in the system databases folder in mssms or start the agent service. I get an error when expanding the system databases folder 'database 'msdb' cannot be opened due to inaccessible files or insufficient memory or disk space.' It's in the same place I put master.


There are some errors in the event log saying the service account failed to open the explicitly specified database, but I have not changed permissions in sql this is the same account that opened it before and the account has full access to the file location. crap crap crap (
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8826 Visits: 16562
Dont panic, what was the exact command you used?
Get the current paths from sys.master_files and compare them

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47365 Visits: 44392
What, exactly, are the messages in the error log?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search