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
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.
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...
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.
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