How to find the data directory for an instance of SQL Server?

Question

We have several huge databases (20 GB +) that mainly contain static search data. Since our application performs joins with tables in these databases, they must be part of each local SQL development server (i.e., they cannot be hosted on a central shared database server).

We plan to copy the canonical set of actual SQL Server database files (* .mdf and * .ldf) and attach them to each local developer database.

What is the best way to find out the local data directory of an instance of SQL Server so that we can copy files to the right place? This will be done using an automated process, so I have to find and use it from the build script.

Answer 1

This depends on whether the default path is set for data files and log files or not.

If the path is explicitly set in Properties => Database Settings => Database default locations , then the SQL server saves it in Software\Microsoft\MSSQLServer\MSSQLServer in DefaultData and DefaultLog values.

However, if these parameters are not explicitly set, the SQL server uses the data and log paths of the main database.

Below is a list of script that covers both cases. This is a simplified version of the query executed by SQL Management Studio.

Also note that I'm using xp_instance_regread instead of xp_regread , so this script will work for any instance, default or name.

 declare @DefaultData nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefaultData output declare @DefaultLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefaultLog output declare @DefaultBackup nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBackup output declare @MasterData nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg0', @MasterData output select @MasterData=substring(@MasterData, 3, 255) select @MasterData=substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData))) declare @MasterLog nvarchar(512) exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SqlArg2', @MasterLog output select @MasterLog=substring(@MasterLog, 3, 255) select @MasterLog=substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog))) select isnull(@DefaultData, @MasterData) DefaultData, isnull(@DefaultLog, @MasterLog) DefaultLog, isnull(@DefaultBackup, @MasterLog) DefaultBackup 

You can achieve the same result using SMO. Bellow is a C # sample, but you can use any other .NET or PowerShell language.

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

This is much simpler in SQL Server 2012 and above if you have the default paths installed (which is probably always correct):

 select InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'), InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath') 

Answer 2

I came across this solution in the documentation for the Create Database statement in the help for SQL 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 

Answer 3

Although this is a very old thread, I feel like I need to make a simple solution. Anytime you know where in Management Studio the parameter that you want to receive for any kind of automated script is located, the easiest way is to run a quick trace of the profiler on a stand-alone test system and record what Management Studio does the backend.

In this case, if you are interested in finding default data and registration locations, you can do the following:

SELECT
SERVERPROPERTY ('instancedefaultdatapath') AS [DefaultFile],
SERVERPROPERTY ('instancedefaultlogpath') AS [DefaultLog]

Answer 4

For the current database, you can simply use:

select physical_name from sys.database_files;

to indicate another database, for example. "Model", use sys.master_files

select physical_name from sys.master_files where database_id = DB_ID(N'Model');

Answer 5

Starting with Sql Server 2012, you can use the following query:

 SELECT SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') as [Default_data_path], SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') as [Default_log_path]; 

(This was taken from a comment on http://technet.microsoft.com/en-us/library/ms174396.aspx and tested.)

Answer 6

Various SQL Server components (data, logs, SSAS, SSIS, etc.) have a default directory. This parameter can be found in the registry. More details here:

http://technet.microsoft.com/en-us/library/ms143547%28SQL.90%29.aspx

So, if you created a database using only CREATE DATABASE MyDatabaseName , it will be created along the path specified in one of the above parameters.

Now, if the administrator / installer changed the default path, then the default path for the instance is stored in the registry in

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\[INSTANCENAME]\Setup

If you know the name of the instance, you can query the registry. This example is specific to SQL 2008 - let me know if you also need the SQL2005 path.

 DECLARE @regvalue varchar(100) EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\Setup', @value_name='SQLDataRoot', @value=@regvalue OUTPUT, @output = 'no_output' SELECT @regvalue as DataAndLogFilePath 

Each database can be created by overriding the server setting in its own location when you issue the CREATE DATABASE DBName with the appropriate parameters. You can find this by running sp_helpdb

 exec sp_helpdb 'DBName' 

Answer 7

Saving simplicity:

 use master select DB.name, F.physical_name from sys.databases DB join sys.master_files F on DB.database_id=F.database_id 

this will return all databases with related files

Answer 8

In the graphical interface: open the properties of your server, go to the "Database Settings" section and select "Default Database Location".

Note that you can drop database files wherever you want, although it seems cleaner to keep them in the default directory.

Answer 9

You can find the default data and log locations for the current instance of SQL Server using the following T-SQL:

 DECLARE @defaultDataLocation nvarchar(4000) DECLARE @defaultLogLocation nvarchar(4000) EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @defaultDataLocation OUTPUT EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @defaultLogLocation OUTPUT SELECT @defaultDataLocation AS 'Default Data Location', @defaultLogLocation AS 'Default Log Location' 

Answer 10

Little nitpick: the data folder is missing, only the default data folder.

In any case, to find it if you want to set the default for the first instance:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ MSSQL.1 \ Setup \ SQLDataRoot

If there is a named instance, MSSQL.1 becomes something like MSSQL10.INSTANCENAME.

Answer 11

Expanding on the answer "splattered bits", here is the complete script that does this:

 @ECHO off SETLOCAL ENABLEDELAYEDEXPANSION SET _baseDirQuery=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; ECHO. SQLCMD.EXE -b -E -S localhost -d master -Q "%_baseDirQuery%" -W >data_dir.tmp IF ERRORLEVEL 1 ECHO Error with automatically determining SQL data directory by querying your server&ECHO using Windows authentication. CALL :getBaseDir data_dir.tmp _baseDir IF "%_baseDir:~-1%"=="\" SET "_baseDir=%_baseDir:~0,-1%" DEL /Q data_dir.tmp echo DataDir: %_baseDir% GOTO :END ::--------------------------------------------- :: Functions ::--------------------------------------------- :simplePrompt 1-question 2-Return-var 3-default-Val SET input=%~3 IF "%~3" NEQ "" ( :askAgain SET /p "input=%~1 [%~3]:" IF "!input!" EQU "" ( GOTO :askAgain ) ) else ( SET /p "input=%~1 [null]: " ) SET "%~2=%input%" EXIT /B 0 :getBaseDir fileName var FOR /F "tokens=*" %%i IN (%~1) DO ( SET "_line=%%i" IF "!_line:~0,2!" == "c:" ( SET "_baseDir=!_line!" EXIT /B 0 ) ) EXIT /B 1 :END PAUSE 

Answer 12

I would do a backup restore simply because it's easier and support version control. Reference data especially needs versions in order to know when they began to operate. Attaching a dettach will not give you this ability. Also with backups, you can continue to provide updated copies without having to close the database.

Answer 13

Alex's answer is correct, but for posterity there is another option: create a new empty database. If you use CREATE DATABASE without specifying the destination directory, you get ... default data directories / logs. Easily.

Personally, however, I would also:

• RESTORE the database on the developer's PC, and not copy / attach (backups can be compressed, exposed to UNC) or
• Use a linked server to avoid this in the first place (depends on how much data goes through the connection)

ps: 20gb is not huge, even in 2015. But all this is relative.

Answer 14

 SELECT DISTINCT dbo.GetDirectoryPath(filename) AS InstanceDataPaths FROM sys.sysaltfiles WHERE filename like '%.mdf' and filename not like '%\MSSQL\Binn\%' SELECT DISTINCT dbo.GetDirectoryPath(filename) AS InstanceLogPaths FROM sys.sysaltfiles WHERE filename like '%.ldf' and filename not like '%\MSSQL\Binn\%' 

You can download a detailed SQL script from how to find the data directory for an instance of SQL Server

Answer 15

You will get the default location if the user database for this query:

 declare @DataFileName nVarchar(500) declare @LogFileName nVarchar(500) set @DataFileName = (select top 1 RTRIM(LTRIM(name)) FROM master.sys.master_files where database_id >4 AND file_id = 1)+'.mdf' set @LogFileName = (select top 1 RTRIM(LTRIM(name)) FROM master.sys.master_files where database_id >4 AND file_id = 2)+'.ldf' select ( SELECT top 1 SUBSTRING(physical_name, 1, CHARINDEX(@DataFileName, LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id >4 AND file_id = 1) as 'Data File' , (SELECT top 1 SUBSTRING(physical_name, 1, CHARINDEX(@LogFileName, LOWER(physical_name)) - 1) FROM master.sys.master_files WHERE database_id >4 AND file_id = 2) as 'Log File'