How to specify server default db propertities

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)

How to specify server default db propertities

Author

Topic

solart
Posting Yak Master

148 Posts

Posted - 2002-10-04 : 16:28:06

SQL 2000, Service Pack 2

Using EM, one can click on the server (left pane) and then select propertities from the dropdown.

From the popup you can click on the Database Settings tab.

There is a frame within the Database Settings tab which allows you to enter the default location for a new database. In the same frame you can specify a location for the new database's log file.

Using T-SQL, how do you modify these defaults?

TIA solart

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-04 : 18:05:40

These settings are stored in the registry . EM uses an undocumented xp called xp_instance_regread to enumerate the relavent keys


xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData'

xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog'

There is also an xp_instance_regwrite xp that will write to the registry e.g.


xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultData',REG_SZ,N'e:\sqldata\data'

xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'DefaultLog',REG_SZ,N'e:\sqldata\log'

These are undocumented (by MS - if you search google you will find articles on how they work)
and unsupported so use at own risk etc....

HTH
Jasper Smith

solart
Posting Yak Master

148 Posts

Posted - 2002-10-07 : 10:49:53

Many Thanks Jasper!!!

solart

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources