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.
| Author |
Topic |
|
brianjensen
Starting Member
22 Posts |
 Posted - 2008-11-04 : 08:34:32
|
| I have a problem with setting the default database location.Not with setting the actual value, but with getting the server to use it within a "Create database" TSQL-statement.If I change the default location for the files, issuing a TSQL "create database" uses the old location, while right-clicking and using the "New database..." option from the menu uses the new location.I've tested this on a couple of servers, and it seems to hold for all installations.The only way I can get TSQL to use the new value is by restarting the SQL service.Is this a bug? And is there some way to fix it? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-04 : 08:53:43
|
| in ssms you can create a T-SQL script that shows you what ssms is doing when creating dbcheck it and see where it differs from yours.or you can run profiler to see the same thing._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
brianjensen
Starting Member
22 Posts |
Posted - 2008-11-04 : 15:52:23
|
| Well the thing is, that a "Create database xx" without any further description should generate a database with the default set data and log file destinations. But it doesn't do that if you change the default locatation without restarting the sql server service. After the restart it works fine. However in SSMS it works fine, but I'm pretty sure that it creates the database with a destination specified for the files thereby not directly using the implicitely specified destination. |
|
|
|
Elisabeth Redei
Starting Member
15 Posts |
Posted - 2008-11-05 : 15:35:18
|
| Hi,This is by design, once you have changed the default location, it will be stored in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer otherwise it is read from internal structures at startup.If it works somewhere without restarting the service, it's because that particular code checks the Registry first.Does this makes sense?/Elisabethelisabeth@sqlserverland.com |
|
|
|
brianjensen
Starting Member
22 Posts |
Posted - 2008-11-06 : 04:17:21
|
| Hi Elisabeth,yes it makes sense, and I also thought that was the reason.But I find it strange that the developers would make such an inconsistency.I would have thought that the same code would be executed "beneath the hood", but it seems that SSMS creates databases based on the new value which must be stored somewhere (with the FILENAME parameters)whereas the TSQL raw "create database" statement uses the value initially read from the registry.Hope they fixed it in SQL2008, I didn't check that yet :) Thank you very much./Brian |
|
|
|
|
|
|
|
|
Database default location bug?