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???
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
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.
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)
My bad. I was connected to the wrong instance. This looks like my solution. Thanks!!!
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.
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?
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.
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.
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.
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...
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?
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.
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.
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
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
1 Person is following this question.
