Find the default file locations WITHOUT accessing the registry


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, 2011 at 01:47 PM in Default

avatar image

24 2 2 4

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

 SELECT * FROM FileLocationTest..sysfiles
 DROP DATABASE FileLocationTest
more ▼

answered Dec 07, 2011 at 02:38 PM

avatar image

25k 3 13 20

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, 2011 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, 2011 at 07:28 AM KenJ

My bad. I was connected to the wrong instance.

This looks like my solution. Thanks!!!

Dec 08, 2011 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, 2011 at 01:42 AM

avatar image

26.2k 18 37 48

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, 2011 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, 2011 at 12:27 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

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, 2011 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, 2011 at 01:49 PM

avatar image

40.9k 39 94 168

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

  FROM sysfiles
Dec 07, 2011 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, 2014 at 01:21 PM

avatar image

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Dec 07, 2011 at 01:47 PM

Seen: 4724 times

Last Updated: Mar 28, 2014 at 01:23 PM

Copyright 2018 Redgate Software. Privacy Policy