Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Change SQL Data Root Directory Expand / Collapse
Author
Message
Posted Monday, August 26, 2013 8:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 76, Visits: 352
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....
Post #1488524
Posted Monday, August 26, 2013 8:46 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,025, Visits: 7,172
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"
Post #1488527
Posted Monday, August 26, 2013 10:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 76, Visits: 352
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?





  Post Attachments 
install.jpg (21 views, 16.72 KB)
Post #1488546
Posted Tuesday, August 27, 2013 3:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 6,737, Visits: 14,369
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"
Post #1488653
Posted Tuesday, August 27, 2013 5:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 76, Visits: 352
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?
Post #1488696
Posted Tuesday, August 27, 2013 6:31 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,025, Visits: 7,172
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"
Post #1488716
Posted Tuesday, August 27, 2013 6:48 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 76, Visits: 352


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.
Post #1488727
Posted Tuesday, August 27, 2013 8:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 12:08 PM
Points: 76, Visits: 352
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 (
Post #1488810
Posted Tuesday, August 27, 2013 11:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 6,737, Visits: 14,369
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"
Post #1488868
Posted Tuesday, August 27, 2013 11:17 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 40,596, Visits: 37,053
What, exactly, are the messages in the error log?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1488869
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse