Unable to Change the Default Physical Location of Content Databases on SQL server


Symptoms


When we change the database default location in SQL server and create a new web application the content database will be saved to the previous location and will not use the settings changed on the SQL server.

Cause


Changes will only take effect after you restart the SQL Server or SQL services. The connection from SharePoint to SQL has to be refreshed.

When you save the location, it's saved as DefaultData and DefaultLog under HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

Resolution


The Work around to resolve this issue is to restart the SQL server or SQL services

1. Open SQL Server Management Studio and connect to the SQL instance Where your SharePoint Databases will be hosted
2. Right Click the Server name or the SQL instance and click Properties.
3. Change Database Settings for "Database Default Locations" for both Data(*.MDF): and Log(*.LDF)
4. Now to test Create a new Database it will get created in the new Database Defult location. But when you create content DB it will create the data files in the default location which you have previously. To aviod that restart teh SQL service or reboot the SQL Box.

More Information