Default location for creating new databases in 7.0

[Schmo]

Does anybody know how to change the DEFAULT location when you are creating a new database in SQL server 7.0. I know there is an ellipse to change them on an individual basis, but that can get pretty annoying. I need to change the actual default location.
Any help would be greatly appreciated.

Thanks

 

[jballum]

I seem to remember that the default location is set to the directory that you originally installed SQL Server.

For example, if it was installed on D:\mssql7 then the default location for new database files would be D:\mssql7\data.

I guess you could search the registries to see if this path exists (I am talking about the full default ..\data path) and maybe change it but I have no idea what kind of impact this would have (on system databases, etc.) so I would just put up with the nuisance of having to change the path for a new database.

JB

 

[LOS]

You could try backing up the Database to a new location, but you would have to create the folders in the new location, i.e Mssql7/Backup and Mssql7/Data in the new location. When you restore the database, you can select the path in the Options Tab and this should tag the database to this new location from that point on.
The easiest method is to create the folders in the new directory, and when creating a new database, target the database to the Mssql7/Data folder in the new location.

 

[malkyb]

Hi - I'm a DBA also trying to change the default location for new databases, so that my team of developers can create new databases and transaction logfiles on separate hard drives, and I can keep these away from 'master'. I've shown them how to do this, but do they remember?
I agree with Schmo that having to define these locations manually can get pretty annoying. Ideally, I would like system databases (master, model, msdb, tempdb) on Hard Drive 1; new user databases on Hard Drive 2 by default; and associated transaction logs on Hard Drive 3 by default.

There is an entry in the System Registry under 'Microsoft\MSSQLServer\MSSQLServer\Parameters' where the startup parameters for locations of master.mdf, mastlog.ldf, and errorlog.log are specified, but that's all I can see.

 

[Hal10K]

Simply do the following:
1) In the Enterprise Manager select the server in question
2) Right click for Server Properties
3) Select the tab "Database Settings"
4) Select the database and tranlog file locations (existing directories) and apply. (I'm speculating that what happens behind the GUI is that the model database is the actual thing changed.)
note: this is predicated that you have system administrator rights for SQL. If the paths are greyed out, you don't have enough rights to the server box. See your friendly LAN administrator. *And* if it can be done in EM, it can be done in T-SQL......

-hal

 

[tlbroadbent]

The default locations are stored in the Registry. SQL 2000 allows changing of the default locations from Enterprise Manager as noted by Hal. SQL 7 requires a Registry update.

HKLM\Software\Microsoft\MSSQLServer\MSSQLServerKey: DefaultData
Key: DefaultLog

Enter the director name with no trailing slash.

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.