Creating Database - How can I find out the Default Data Directory?

Question

I want to be able to create a database within my C# WinForm application using the code I found HERE.

But I need to find a way to get the default data directory for that particular SQL Server Instance. I am wondering if there was a easy way to accomplish this that is able to be used on the various versions of SQL Server.

Thanks in advance.

EDIT

I found the following Select that will return the default data directory on the remote server:

SELECT 
    SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM master.sys.master_files 
WHERE database_id = 1 
    AND file_id = 1

This solution will only work on SQL Server 2005+.

**

Do you really need to know the default data directory? Do you need physical access to the database once created? — adrianbanks, Jul 23 '12 at 22:46
I am sending Create Database SQL down the pipe to the remote server and two of the requirements are the locations of the MDF and LDF files: http://msdn.microsoft.com/en-us/library/ms176061.aspx. — Mark Kram, Jul 23 '12 at 23:10
If you don't specify the file paths (e.g. `CREATE DATABASE test`), the files will go into the default configured location anyway - no need to explicitly state where they should go unless you want them somewhere else. — adrianbanks, Jul 23 '12 at 23:12
Hmmm, I wasn't aware of that. I did find a Select query that will return the default path. I will update my Original post. — Mark Kram, Jul 23 '12 at 23:34
I'm still not sure why you need the directory. The query you have put above will find the directory in which the master database lives, not the default data directory (e.g. if it has been configured since master was created). You can just create a database without specifying the location to get it to go to the default location. If you do still need the location, create the database first and then get its location. — adrianbanks, Jul 24 '12 at 09:52
@adrianbanks That is not the case for me. If I tried to do something like `CREATE DATABASE MyD ON PRIMARY (NAME = MyD_Data, FILENAME = 'MyDData.mdf', SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) LOG ON (NAME = MyD_Log, FILENAME = 'MyDLog.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)` it would fail. However, if I did `CREATE DATABASE MyD ON PRIMARY (NAME = MyD_Data, FILENAME = 'C:\\MyDData.mdf', SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) LOG ON (NAME = MyD_Log, FILENAME = 'C:\\MyDLog.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)` it's perfectly fine. — Arvo Bowen, Dec 07 '16 at 23:15

dmigo · Answer 1 · 2022-10-02T18:12:59.530

Here is a better solution suggested by Alex Aza.
The solution is:

using (var connection = new SqlConnection("Data Source=.;Integrated Security=SSPI"))
{
    var serverConnection = new ServerConnection(connection);
    var server = new Server(serverConnection);
    var defaultDataPath = string.IsNullOrEmpty(server.Settings.DefaultFile) ? server.MasterDBPath : server.Settings.DefaultFile;
    var defaultLogPath = string.IsNullOrEmpty(server.Settings.DefaultLog) ? server.MasterDBLogPath : server.Settings.DefaultLog;
}

It requires Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.ConnectionInfo.dll to be referenced. One can find these dlls in folder C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies. They are distributed with SQL installation.

score 0 · Answer 2 · answered Aug 09 '12 at 12:59

0

This works with SQL 2000+

SELECT SUBSTRING(filename, 1, CHARINDEX(N'master.mdf', LOWER(filename)) - 1)
FROM master.dbo.sysdatabases
WHERE name = 'master'

answered Aug 09 '12 at 12:59

Michael Maramzin

1

score 0 · Answer 3 · answered Aug 09 '12 at 13:14

0

You can find it on Server Properties - Database settings - Database default location. You can use SQL Server Profiler to find out sql query, if you need it (it depends on server version).

answered Aug 09 '12 at 13:14

ceth

44,198
62
180
289

score 0 · Answer 4 · answered Apr 16 '14 at 09:14

Maybe this would help you:

DECLARE @regvalue varchar(100)

EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
        @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLServer\Setup',
        @value_name='SQLDataRoot',
        @value=@regvalue OUTPUT,
        @output = 'no_output'

SELECT @regvalue as DataAndLogFilePath

This stored procedure need one parameter in which is stated SQL version, ie. ...\MSSQL.11.MSSQLSERVER\...

Creating Database - How can I find out the Default Data Directory?

EDIT

4 Answers4