question

MagneticDave avatar image
MagneticDave asked

Find the default file locations WITHOUT accessing the registry

Hello, I am trying to script our development database so we can easily refresh the copies on our individual developer machines. The problem is trying to CREATE DATABASE in the default file location when those locations vary. I cannot use the scripts that pull from the registry, as there are often several instances of SQL on the developer machine. I need a way to set a variable to the default file location for the specific instance it is run under. Can anyone help???
database-filesdefaultregistry
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
If your system databases aren't in the default locations, you can create a temporary database then query sysfiles to find out where the files were put. Once you have the file locations, drop the temporary database... CREATE DATABASE FileLocationTest SELECT * FROM FileLocationTest..sysfiles DROP DATABASE FileLocationTest
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MagneticDave avatar image MagneticDave commented ·
Actually, I tested that script and it pulls the location of the master database. So, evidently, the default file location is only implemented when you create the database from SSMS.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Actually, it's implemented in both TSQL and SSMS. I just ran it to be sure. Pick a single instance to run the script on and compare the output from the query with what you see in the "Database Settings" portion of the "Server Properties" window for the same instance in SSMS. If the paths don't match, something is broken (you have to restart for a new default path setting to take effect)
0 Likes 0 ·
MagneticDave avatar image MagneticDave commented ·
My bad. I was connected to the wrong instance. This looks like my solution. Thanks!!!
0 Likes 0 ·
Tim avatar image
Tim answered
You should be able to get the path from sysfiles for master and use parse that path for where to put the rest of your databases if you want to put them in that location. Of course you will get some options on here that you can do everything you are wanting to do with Powershell which depending on what all you are trying to accomplish, it might not be a bad idea.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MagneticDave avatar image MagneticDave commented ·
Thanks, but that will only tell me what the path is for the master (or model) database. I need to script the current default database setting. How would I use PowerShell to do that?
0 Likes 0 ·
Tim avatar image Tim commented ·
What I understood from your question was you need to create a database on individual local developer workstations and have the database be created in the default path of the instance you are running the create statement on. If that is the case, you can in fact use sysfiles to get the path of master or model. You can substring that value to get the path, put that in a variable and use it for your create statement.
0 Likes 0 ·
Tim avatar image Tim commented ·
Something like this. You can specify data by fileid 1 and log by fileid 2 to use two variables if you have data and logs on different drives. SELECT SUBSTRING(Filename, 1,(LEN(FILENAME) - CHARINDEX('\', REVERSE(FILENAME))+1)) FROM sysfiles
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Why do you need to know where the default location is? Look at @KenJ s answer, if you simply create a database, it will be created in those locations, there's no need to know up front.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MagneticDave avatar image MagneticDave commented ·
Yes there is. I need to script initial file sizes and growths, but cannot use the detailed CREATE script unless I include the filepath. Also, simply creating the database puts the files in the ORIGINAL master database locations, not the currently set default locations.
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
Why can't you query the registry? There's a key in there to provide a lookup between SQL Server instance name and registry sub-tree - `HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL` - That'll tell you what instances are installed, and where to find their defaults within the main `HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server` registry area. It's just a matter of making your script a little more intelligent...
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MagneticDave avatar image MagneticDave commented ·
Okay, but instance name will vary. (See my replys to other comments.) So, how do I find the instance name from within the SQL script?
0 Likes 0 ·
WilliamD avatar image
WilliamD answered
To add to these answers; you seem to have a standardisation issue. If you want to automate your systems (regardless of their usage), you need to implement standards, including the installation/setup of SQL Server on the dev machines. If you don't standardise, you will forever be tweaking your admin/automation scripts for the strange edge-cases that you **will** encounter. You can go down the road suggested by @Kev, running a plain CREATE DATABASE command, allowing the SQL Server instance to decide where to place the files, but I would rather go the other way and configure the instances according to a standard. That way, everything behaves in a uniform manner.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MagneticDave avatar image MagneticDave commented ·
Unfortunately we will also be distributing to clients, which will not be standardized. So we want to NOT standardize in house to better test variations.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
You can see what I found out when I asked a similar question [here][1] [1]: http://ask.sqlservercentral.com/questions/36685/getting-basic-info-via-powershell
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

nani1211 avatar image
nani1211 answered
Hi all, From SQL 2012 you can user Serverproperty to get default data log file location select SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile], SERVERPROPERTY('instancedefaultlogpath') AS [DefaultLog] Thanks, Brahma
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.