Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Change SQL Server Database files path

Posted on 2014-09-16
9
Medium Priority
?
221 Views
Last Modified: 2014-09-16
On one of the server  I have SQL Server 2012 and my client forced me to a different drive to store the database files (.ldf,.mdf...)

I need to know if I change the default path for these files and put the new path.
Would it be recommended or safe to do so.
0
Comment
Question by:yadavdep
  • 4
  • 3
  • 2
9 Comments
 
LVL 6

Accepted Solution

by:
Mandeep Singh earned 1000 total points
ID: 40327269
Default path is mentioned on installation time.

Safety is depend on configuration done: like RAID 0, RAID 1, RAID 5 or RAID 10.
0
 

Author Comment

by:yadavdep
ID: 40327270
Hi Mandeep,


yes my client has applied Raid 1 on E: drive (something like this only) and he wants me to use e: drive only for database.
0
 
LVL 29

Expert Comment

by:becraig
ID: 40327273
Are you referencing system databases or specific databases you will use for an application ?

If the latter, simply go to the management studio and select to attach a database and point to the log and database files.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 

Author Comment

by:yadavdep
ID: 40327276
I need to store only the database I create to store on E:  drive, not for system database
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40327282
Actually RAID 1 is used for Excellent redundancy and you also get a good performance on data read but data write may be a bit slow.

so i think you have to use E: drive because of Excellent redundancy
0
 
LVL 29

Assisted Solution

by:becraig
becraig earned 1000 total points
ID: 40327289
Simply go to the management studio and select to attach a database and point to the log and database files you now have on the E drive.
0
 

Author Comment

by:yadavdep
ID: 40327350
One strange thing is happening.
In SQL server management studio, I right clicking Server name and going to Properties, then Database Settings.

When I change the default path, it only accepting the new path for Backup.
For Data and Log files path it keeps reverting my changes.

I change the path clicks on Ok, when I come come back and check it again it, both Data and log files path reverts back to previous values.
0
 
LVL 6

Expert Comment

by:Mandeep Singh
ID: 40327367
After path change restart SQL Server Services will fix this.
0
 

Author Comment

by:yadavdep
ID: 40327376
Thanks Mandeep, your solution work perfectly.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windows Server 2003 introduced persistent Volume Shadow Copies and made 2003 a must-do upgrade.  Since then, it's been a must-implement feature for all servers doing any kind of file sharing.
This tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question