How do I find the data directory for a SQL Server instance

Question

We have a few huge databases  20GB   which mostly contain static lookup data   Because our application executes joins against tables in these databases  they have to be part of each developers local SQL Server  i e  they can t be hosted on a central  shared database server      We plan on copying a canonical set of the actual SQL Server database files    mdf and   ldf  and attach them to each developer s local database   What s the best way to find out the local SQL Server instance s data directory so we can copy the files to the right place   This will be done via an automated process  so I have to be able to find and use it from a build script

User · Answer

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 detail SQL script from how to find the data directory for a SQL Server instance

User · Answer

I stumbled 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

User · Answer

Keeping it simple   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 associated files

User · Answer

Alex s answer is the right one  but for posterity here s another option  create a new empty database  If you use CREATE DATABASE without specifying a target dir you get    the default data   log directories  Easy   Personally however I d probably either    RESTORE the database to the developer s PC  rather than copy attach  backups can be compressed  exposed on a UNC   or Use a linked server to avoid doing this in the first place  depends how much data goes over the join    ps  20gb is not huge  even in 2015  But it s all relative

User · Answer

You can find default Data and Log locations for the current SQL Server instance by 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

User · Answer

Various components of SQL Server  Data  Logs  SSAS  SSIS  etc  have a default directory  The setting for this can be found in the registry  Read more here   http   technet microsoft com en-us library ms143547 28SQL 90 29 aspx  So if you created a database using just CREATE DATABASE MyDatabaseName it would be created at the path specified in one of the settings above   Now  if the admin   installer changed the default path  then the default path for the instance is stored in the registry at  HKEY LOCAL MACHINE SOFTWARE Microsoft Microsoft SQL Server  INSTANCENAME  Setup  If you know the name of the instance then you can query the registry  This example is SQL 2008 specific - let me know if you need the SQL2005 path as well   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 overriding the server setting in a it s own location when you issue the CREATE DATABASE DBName statement with the appropriate parameters  You can find that out by executing sp helpdb  exec sp helpdb  DBName

User · Answer

As of 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 at http   technet microsoft com en-us library ms174396 aspx  and tested

User · Answer

Expanding on  splattered bits  answer  here is a complete script that does it    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  gt data dir tmp IF ERRORLEVEL 1 ECHO Error with automatically determining SQL data directory by querying your server amp 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

User · Answer

It depends on whether default path is set for data and log files or not   If the path is set explicitly at Properties    Database Settings    Database default locations then SQL server stores it at Software Microsoft MSSQLServer MSSQLServer in  DefaultData and DefaultLog values   However  if these parameters aren t set explicitly  SQL server uses Data and Log paths of master database   Bellow is the script that covers both cases  This is simplified version of the query that SQL Management Studio runs   Also  note that I use xp instance regread instead of xp regread  so this script will work for any instance  default or named   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 by using SMO  Bellow is C  sample  but you can use any other  NET language or PowerShell   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 is so much simpler in SQL Server 2012 and above  assuming you have default paths set  which is probably always a right thing to do    select      InstanceDefaultDataPath   serverproperty  InstanceDefaultDataPath        InstanceDefaultLogPath   serverproperty  InstanceDefaultLogPath

User · Answer

For the current database you can just use   select physical name fromsys database files   to specify another database e g   Model   use sys master files  select physical name from sys master files where database id   DB ID N Model

User · Answer

You will get default location if user database by 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  gt 4 AND file id   1    mdf  set  LogFileName      select top 1 RTRIM LTRIM name   FROM master sys master files where database id  gt 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  gt 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  gt 4 AND file id   2   as  Log File

User · Answer

Small nitpick  there is no data folder  only a default data folder   Anyway  to find it  assuming you want to install for the first default instance   HKEY LOCAL MACHINE SOFTWARE Microsoft Microsoft SQL Server MSSQL 1 Setup SQLDataRoot  If there s a named instance  MSSQL 1 becomes something like MSSQL10 INSTANCENAME

User · Answer

From the GUI  open your server properties  go to Database Settings  and see Database default locations   Note that you can drop your database files wherever you like  though it seems cleaner to keep them in the default directory

User · Answer

Even though this is a very old thread  I feel like I need to contribute a simple solution  Any time that you know where in Management Studio a parameter is located that you want to access for any sort of automated script  the easiest way is to run a quick profiler trace on a standalone test system and capture what Management Studio is doing on the backend   In this instance  assuming you are interested in finding the default data and log locations you can do the following   SELECT  nbsp  nbsp  nbsp  nbsp  nbsp SERVERPROPERTY  instancedefaultdatapath   AS  DefaultFile     nbsp  nbsp  nbsp  nbsp  nbsp SERVERPROPERTY  instancedefaultlogpath   AS  DefaultLog

User · Answer

i would have done a backup restore simply becuase its easier and support versioning  Reference data especially needs to be versioned in order to know when it started taking effect  A dettach attach wont give you that ability  Also with backups you can continue to provide updated copies without having to shut down the database

[sql] How do I find the data directory for a SQL Server instance?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to sql-server-2005

Examples related to sql-server-2008-r2