January 16, 2012 at 1:53 am
Hi Friends,
I came across strange thing on sql 2008 cluster.
I have a 2 - node sql 2008 sp2 cluster.
node namde
sqlnode1\
sqlnode2
build no : 10.0.4000
during the setup the default data dir is set to
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL10.<instancemae>\Setup
SQLDataRoot: N:\MSSQL10.<Instancename>\MSSSQL
So, currently my all the system n user db's inclusing msdb certificate are resided under
N:\MSSQL10.<Instancename>\MSSQL\DATA
Now, this is what my doubt is.
currently sql is running on sqlnode1
I changed the default database settings in sql server management studio
Data: N:\SQLDUMP\DATA
Log: N:\SQLDUMP\Log
Hoping that the newly created databases must be created in these new locations.
i double checked
Hklm\software\microsoft\microsoft sql server\mssql10.instancename\MSSqlserver
DefaultData : N:\SQLDUMP\DATA
DefaultLog : N:\SQLDUMP\Log
Both have been reflected.
Next, i tried creating a new database. Here i am expecting the database files
should be created under new chnaged location N:\sqldump\data but this is not happening
and database are getting created under "SQLDataRoot"
i.e. N:\MSSQL10.<Instancename>\MSSQL\DATA.
I would like to know the reason why this happening which is not supposed to ????
Any help would be greatly appreciated.
Thanks in Advance.
January 16, 2012 at 2:53 am
How are you creating your database? is it using script or from SSMS
if it is using script ( Create database db1 ) then it still places data and log files in Root directory.
If it is using SSMS then SSMS prompts it with new Default locations.
Regards,
Raj
January 16, 2012 at 3:36 am
Hi Raj,
i am using
use master
go
create database db1
go
But , what is the need in changing the default locations?
January 16, 2012 at 10:29 am
Have you restarted the SQL Server service? This must be restarted for the changes to take place.
Jared
CE - Microsoft
January 19, 2012 at 4:26 am
Restart worked. Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply