Geek asks and answers

Creating a Database - How do I find out the default data directory?

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

But I need to find a way to get the default data directory for this particular instance of SQL Server. I am wondering if there was an easy way to achieve this that can be used for different versions of SQL Server.

Thanks in advance.

EDIT

I found the following Select, which 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+.

**

+5
source share
4 answers

This works with SQL 2000+

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

- - . SQL Server Profiler sql-, ( ).

0

- , .
:

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;
}

Microsoft.SqlServer.Smo.dll Microsoft.SqlServer.ConnectionInfo.dll. DLL C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies. SQL.

0

, :

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

, SQL, .. ...\MSSQL.11.MSSQLSERVER\...

0

More articles:


All Articles