Default Location. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Default Location.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-13 : 09:50:08

Chris Evans writes "I have a question of the location as to where the location of a default create database statement creates it's files.

I have noticed that when I set the defaults in SQL Server to locate the Data and log files it does not take for when running a "Create Database Test" in Query analyzer.

It seems to dump those files into the location where the model and master databases are located.

My question is, is there a registry key or a field in the master/model database that can be changed to move the default locations of the log and data files.

When setting the defaults for this in the Enterprise manager it seems it only uses those defaults for creating DB's in Enterprise manager.

Any suggestions or help would be greatly appreciated.

Chris."

solart
Posting Yak Master

148 Posts

Posted - 2002-09-13 : 10:47:57

Hummm, assuming what you say is true, maybe:
a. backing up the Model database
b. Restoring database, overriding locations to be what you want
c. Then try Q/A.

HTHs solart

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-09-13 : 10:55:51

You need to restart the SQL Service for the change to be visible from Query Analyzer it seems (sql2000). As you say, EM seems to pick it up straight away. Looking at the registry whilst making the change via EM it definately changes the registry straight away which is where EM is picking it up from .Looking in Profiler I can see that EM calls xp_instance_regread to get the default data and log directories (from the updated registry) and formats a create database statement like


CREATE DATABASE [t2]  
ON 
PRIMARY (NAME = N't2_Data', FILENAME = N'C:\1\t2_Data.MDF' ,SIZE = 1, FILEGROWTH = 10%) 
LOG ON (NAME = N't2_Log', FILENAME = N'C:\2\t2_Log.LDF',SIZE = 1, FILEGROWTH = 10%)

wheras when just run in Query Analyzer the trace shows

 CREATE DATABASE t2

I can only presume that the default locations are somehow cached on server startup.

Edited by - jasper_smith on 09/13/2002 11:30:29

solart
Posting Yak Master

148 Posts

Posted - 2002-09-13 : 11:20:25

Thanks Jasper!

But Chris, I must admit I am a little confused.

"I have noticed that when I set the defaults in SQL Server to locate the Data and log files it does not take for when running a "Create Database Test" in Query analyzer."

How are the defaults being set (what steps)?

Thanks solart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources