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

Ответ 1

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

Если путь задан явно в Properties = > Database Settings = > Database default locations, тогда SQL-сервер сохраняет его в Software\Microsoft\MSSQLServer\MSSQLServer в DefaultData и DefaultLog значениях.

Однако, если эти параметры не заданы явно, SQL-сервер использует пути данных и журнала основной базы данных.

Ниже представлен список script, который охватывает оба случая. Это упрощенная версия запроса, выполняемого SQL Management Studio.

Также обратите внимание, что я использую xp_instance_regread вместо xp_regread, поэтому этот script будет работать для любого экземпляра, по умолчанию или имени.

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

Вы можете добиться того же результата, используя SMO. Bellow - образец С#, но вы можете использовать любой другой язык .NET или 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;
}

Это намного проще в SQL Server 2012 и выше, если у вас установлены установленные по умолчанию пути (что, вероятно, всегда правильно):

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

Ответ 2

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

Ответ 3

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

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

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

Ответ 4

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

select physical_name from sys.database_files;

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

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

Ответ 5

Начиная с 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 и протестировано.)

Ответ 6

Различные компоненты 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',
        @[email protected] OUTPUT,
        @output = 'no_output'

SELECT @regvalue as DataAndLogFilePath

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

exec sp_helpdb 'DBName'

Ответ 7

Сохранение простоты:

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

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

Ответ 8

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

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

Ответ 9

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

Ответ 10

Маленькая nitpick: папка с данными отсутствует, только папка данных по умолчанию.

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

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

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

Ответ 11

Развернувшись на ответ "splattered bits", вот полный script, который делает это:

@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

Ответ 12

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

Ответ 13

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

Лично, однако, я бы тоже:

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

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

Ответ 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\%'

Вы можете загрузить подробный SQL script из как найти каталог данных для экземпляра SQL Server

Ответ 15

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

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'