Как найти каталог данных для экземпляра SQL Server?

я наткнулся на это решение в документации для Инструкции Create Database в справке для 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

хотя это очень старый поток, я чувствую, что мне нужно внести свой вклад в простое решение. Каждый раз, когда вы знаете, где в Management Studio находится параметр, к которому вы хотите получить доступ для любого автоматизированного сценария, самый простой способ-запустить быструю трассировку профилировщика в Автономной тестовой системе и захватить то, что Management Studio делает на бэкэнде.

в этом случае, предполагая, что вы заинтересованы в поиске расположения данных и журналов по умолчанию, вы можете сделать следующее:

выберите
     SERVERPROPERTY ('instancedefaultdatapath') как [DefaultFile],
     SERVERPROPERTY ('instancedefaultlogpath') как [DefaultLog]

для текущей базы данных вы можете просто использовать:

select physical_name fromsys.database_files;

чтобы указать другую базу данных, например "модель", используйте sys.master_files

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

начиная с Sql Server 2012, Вы можете использовать следующий запрос:

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

(Это было взято из комментария в http://technet.microsoft.com/en-us/library/ms174396.aspx, и проверено.)

различные компоненты SQL Server (данные, журналы, SSAS, SSIS и т. д.) имеют каталог по умолчанию. Настройки для этого можно найти в реестре. Подробнее читайте здесь:

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

так что если вы создали базу данных, используя только CREATE DATABASE MyDatabaseName он будет создан по пути, указанному в одной из настроек выше.

теперь, если администратор / установщик изменил путь по умолчанию, то по умолчанию путь к экземпляру хранится в реестре за

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

если вы знаете имя экземпляра, то вы можете запросить реестр. Этот пример специфичен для SQL 2008-дайте мне знать, если вам также нужен путь SQL2005.

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

каждая база данных может быть создана переопределяя настройки сервера в его собственном месте, когда вы выдаете CREATE DATABASE DBName заявление с соответствующими параметрами. Вы можете узнать это, выполнив процедуру sp_helpdb

exec sp_helpdb 'DBName'

все просто:

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

это вернет все базы данных с соответствующими файлами

из графического интерфейса: откройте свойства сервера, перейдите к Параметры Базы Данных и видим расположение базы данных по умолчанию.

обратите внимание, что вы можете удалить файлы базы данных, где вам нравится, хотя это кажется чище, чтобы держать их в каталоге по умолчанию.

вы можете найти расположение данных и журналов по умолчанию для текущего экземпляра SQL Server, используя следующий 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'

маленький придурок: нет папки данных, только по умолчанию папку data.

в любом случае, чтобы найти его, если вы хотите установить для первого экземпляра по умолчанию:

раздел HKEY_LOCAL_MACHINE\программное обеспечение\Майкрософт\SQL-сервера Microsoft\MSSQL данных.1\Setup\SQLDataRoot

Если есть именованный экземпляр, MSSQL.1 становится чем-то вроде MSSQL10.Имя экземпляра.

расширяя ответ" разбрызганные биты", вот полный скрипт, который это делает:

@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

Я бы сделал резервное восстановление просто потому,что его проще и поддержка версий. Справочные данные особенно должны быть версионными, чтобы знать, когда они начали вступать в силу. Прикрепление dettach не даст вам эту способность. Кроме того, с помощью резервных копий вы можете продолжать предоставлять обновленные копии без необходимости завершения работы базы данных.

Алекс - Это верно, но для потомков вот еще один вариант: создать новую пустую базу данных. Если вы используете CREATE DATABASE без указания целевого каталога, который вы получаете... каталоги данных / журналов по умолчанию. Простой.

лично я бы, наверное, тоже:

• восстановить базу данных на ПК разработчика, а не копировать/присоединять (резервные копии могут быть сжаты, выставлены на UNC) или
• использовать связанный сервер, чтобы избежать этого в первую очередь (зависит от того, сколько данных проходит через присоединиться)

ps: 20gb не является огромным, даже в 2015 году. Но это все относительно.

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\%'

вы можете скачать подробный SQL-скрипт из как найти каталог данных для экземпляра SQL Server

вы получите расположение по умолчанию, если пользовательская база данных по этому запросу:

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'