x

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???
more ▼

asked Dec 07 '11 at 01:47 PM in Default

MagneticDave gravatar image

MagneticDave
14 2 2 3

(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

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
more ▼

answered Dec 07 '11 at 02:38 PM

KenJ gravatar image

KenJ
19.1k 1 3 11

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.
Dec 08 '11 at 06:49 AM MagneticDave

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)
Dec 08 '11 at 07:28 AM KenJ

My bad. I was connected to the wrong instance.

This looks like my solution. Thanks!!!
Dec 08 '11 at 08:32 AM MagneticDave
(comments are locked)
10|1200 characters needed characters left

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

answered Dec 08 '11 at 01:42 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

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.
Dec 08 '11 at 07:21 AM MagneticDave
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 08 '11 at 12:27 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

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.
Dec 08 '11 at 07:20 AM MagneticDave
(comments are locked)
10|1200 characters needed characters left

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

answered Dec 07 '11 at 01:49 PM

Tim gravatar image

Tim
35.5k 32 40 138

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?
Dec 07 '11 at 02:12 PM MagneticDave
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.
Dec 07 '11 at 02:17 PM Tim

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
Dec 07 '11 at 02:36 PM Tim
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Mar 28 at 01:21 PM

nani1211 gravatar image

nani1211
11 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x10
x7
x3

asked: Dec 07 '11 at 01:47 PM

Seen: 2066 times

Last Updated: Mar 28 at 01:23 PM