August 26, 2013 at 8:33 pm
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?
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....
August 26, 2013 at 8:46 pm
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; Theyll drag you down to their level and beat you with experience
August 26, 2013 at 10:32 pm
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\MSSQLand 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?
August 27, 2013 at 3:56 am
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" 😉
August 27, 2013 at 5:57 am
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?
August 27, 2013 at 6:31 am
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; Theyll drag you down to their level and beat you with experience
August 27, 2013 at 6:48 am
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.
August 27, 2013 at 8:47 am
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 (
August 27, 2013 at 11:16 am
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" 😉
August 27, 2013 at 11:17 am
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
August 27, 2013 at 11:21 am
Placing system dbs on C is not too dramatic as the boot drive it will always be online while the server is up. Obviously tempdb and distribution db should be separated.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 27, 2013 at 11:36 am
Well I may have just caught a break. Thanks for your quick responses guys but there probably isn't a point in copying and pasting all the errors now. I was told that only a couple servers need to have their system databases moved, and none of them actually host any data yet. They are all brand new installs, so far it's been quicker and cleaner to just remove SQL and install it all over again with the correct data paths listed.
I thought I was going to have to change about 10 servers that all had been updated with server packs and the systems guys where going to have to reinstall them all. It looks like I notified them in time so they where able to change their install plan for the majority of the servers. Thanks again guys, and yeah I was freaking out a bit.
August 27, 2013 at 11:38 am
Well that is good news, however if you post the errors the people willing to help just might be able to help you through the situation...so in the future should you ever need to do something like this again, you will be better prepared?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 14, 2016 at 2:27 pm
Since we will get rid of T drive which I had put as SQL Data Root Directory when I installed SQL 2012SP1 before, so I have moved all system databases from (T drive) drive to another SAN drive (R: drive) and brought up sql running successfully. But I have following concerns before we get rid of T drive...
1. Can I just copy/paste other files /folders over to the R drive without impact bring up sql server?
Other files/folders are (5 MS_AgentSignnningCertificate% files on DATA folder) or folders (Backup, repldata (no data at all) and FTData folder which has 1 folder 'filterData' created on the date installed, and many other old date xml files)
2. The registry key of 'SQLDataRoot' under \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup\ is still showing the old SQL Data root directory 'T:\SQL2012ENT64\SYSTEMDB\MSSQL11.MSSQLSERVER\MSSQL' which I setup before. Is this going to impact sql server running after we get rid of T drive? Or, is there anyway to modify it to the right new drive R without from regedit?
3. Do we have to use exact same folder format (ending with MSSQL11.MSSQLSERVER\MSSQL\) for the new data root directory? I moved all system databases files to the new drive R:\SQL2012ENT64\SYSTEMDB\DATA without any problem to bring up sql server. but not sure if it'll be ok after copy all files over?
April 14, 2016 at 2:28 pm
Since we will get rid of T drive which I had put as SQL Data Root Directory when I installed SQL 2012SP1 before, so I have moved all system databases from (T drive) drive to another SAN drive (R: drive) and brought up sql running successfully. But I have following concerns before we get rid of T drive...
1. Can I just copy/paste other files /folders over to the R drive without impact bring up sql server?
Other files/folders are (5 MS_AgentSignnningCertificate% files on DATA folder) or folders (Backup, repldata (no data at all) and FTData folder which has 1 folder 'filterData' created on the date installed, and many other old date xml files)
2. The registry key of 'SQLDataRoot' under \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\Setup\ is still showing the old SQL Data root directory 'T:\SQL2012ENT64\SYSTEMDB\MSSQL11.MSSQLSERVER\MSSQL' which I setup before. Is this going to impact sql server running after we get rid of T drive? Or, is there anyway to modify it to the right new drive R without from regedit?
3. Do we have to use exact same folder format (ending with MSSQL11.MSSQLSERVER\MSSQL\) for the new data root directory? I moved all system databases files to the new drive R:\SQL2012ENT64\SYSTEMDB\DATA without any problem to bring up sql server. but not sure if it'll be ok after copy all files ove
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply