SQL Server 인스턴스의 데이터 디렉토리를 찾는 방법은 무엇입니까?

SQL Server 인스턴스의 데이터 디렉토리를 찾는 방법은 무엇입니까?

정적 룩업 데이터가 대부분 포함된 몇 개의 거대한 데이터베이스(20GB 이상)가 있습니다.애플리케이션은 이러한 데이터베이스의 테이블에 대해 조인을 실행하므로 각 개발자 로컬 SQL 서버의 일부여야 합니다(즉, 중앙 공유 데이터베이스 서버에서 호스팅할 수 없음).

실제 SQL Server 데이터베이스 파일(*.mdf 및 *.ldf)의 표준 세트를 복사하여 각 개발자의 로컬 데이터베이스에 첨부할 계획입니다.

파일을 올바른 위치에 복사할 수 있도록 로컬 SQL 서버 인스턴스의 데이터 디렉토리를 찾는 가장 좋은 방법은 무엇입니까?이 작업은 자동화된 프로세스를 통해 수행되므로 빌드 스크립트에서 찾아 사용할 수 있어야 합니다.

데이터 및 로그 파일에 기본 경로가 설정되어 있는지 여부에 따라 달라집니다.

으로 로가명시설정경우로 설정되어 Properties=>Database Settings=>Database default locations 다음 는 그런다음저 SQL다니에 합니다.Software\Microsoft\MSSQLServer\MSSQLServerDefaultData그리고.DefaultLog가치.

그러나 이러한 매개 변수가 명시적으로 설정되지 않은 경우 SQL 서버는 마스터 데이터베이스의 데이터 및 로그 경로를 사용합니다.

다음은 두 가지 경우를 모두 다루는 스크립트입니다.SQL Management Studio에서 실행하는 쿼리의 단순화된 버전입니다.

저는 한또내사노트는하용가를 합니다.xp_instance_regread에 xp_regread이 스크립트는 기본값 또는 명명된 모든 인스턴스에 대해 작동합니다.

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는 C# 샘플이지만 다른 .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')

비록 이것이 매우 오래된 스레드이지만, 저는 간단한 해결책을 제공해야 한다고 생각합니다.Management Studio에서 자동화된 스크립트 유형에 대해 액세스할 매개 변수가 있는 위치를 알 때마다 독립 실행형 테스트 시스템에서 빠른 프로파일러 추적을 실행하고 Management Studio가 백엔드에서 수행하는 작업을 캡처하는 것이 가장 쉬운 방법입니다.

이 경우 기본 데이터 및 로그 위치를 찾는 데 관심이 있다고 가정하면 다음을 수행할 수 있습니다.

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

SQL 서버에 대한 도움말에 있는 데이터베이스 생성 문에 대한 설명서에서 이 솔루션을 우연히 발견했습니다.

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

현재 데이터베이스의 경우 다음을 사용할 수 있습니다.

select physical_name fromsys.database_files;

다른 데이터베이스(예: 'Model')를 지정하려면 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과 관련이 있습니다. SQL 2005 경로도 필요하면 알려주십시오.

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_helpdbsp_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

연결된 파일이 있는 모든 데이터베이스를 반환합니다.

GUI에서: 서버 등록 정보를 열고 데이터베이스 설정으로 이동한 후 데이터베이스 기본 위치를 참조하십시오.

데이터베이스 파일을 원하는 위치에 놓을 수 있지만 기본 디렉토리에 보관하는 것이 더 깨끗합니다.

작은 트집: 데이터 폴더는 없고 기본 데이터 폴더만 있습니다.

어쨌든 첫 번째 기본 인스턴스에 대해 설치하려는 경우 이 인스턴스를 찾으려면 다음과 같이 하십시오.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\설정\SQL 데이터 루트

명명된 인스턴스가 있는 경우 MSSQL.1은 MSSQL10.INSTANNAME과 같은 인스턴스가 됩니다.

다음 T-SQL을 사용하여 현재 SQL Server 인스턴스의 기본 데이터 및 로그 위치를 찾을 수 있습니다.

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'

"스플래닝 비트" 답변을 확장하면 다음과 같은 작업을 수행하는 전체 스크립트가 있습니다.

@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

백업 복원이 더 쉽고 버전 관리를 지원하기 때문에 백업 복원을 수행했을 것입니다.참조 데이터가 언제부터 적용되었는지 알기 위해서는 특히 버전을 변경해야 합니다.첨부파일이 당신에게 그런 능력을 주지는 않습니다.또한 백업을 사용하면 데이터베이스를 종료하지 않고도 업데이트된 복사본을 계속 제공할 수 있습니다.

알렉스의 대답은 옳지만, 후손들을 위한 또 다른 선택지가 있습니다. 빈 데이터베이스를 새로 만드는 것입니다.대상 dir를 지정하지 않고 CREATE DATABASE를 사용하면...기본 데이터/로그 디렉터리만만하다.

하지만 개인적으로는 다음 중 하나일 것입니다.

• 복사/첨부(백업을 압축하거나 UNC에 노출할 수 있음) 대신 데이터베이스를 개발자의 PC에 복원합니다.
• 연결된 서버를 사용하여 처음부터 이 작업을 수행하지 않도록 합니다(가입을 통해 전송되는 데이터 양에 따라 다름).

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 Server 인스턴스의 데이터 디렉토리를 찾는 방법에서 상세 SQL 스크립트를 다운로드할 수 있습니다.

다음 쿼리를 사용하여 사용자 데이터베이스를 사용할 경우 기본 위치를 얻을 수 있습니다.

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'

언급URL : https://stackoverflow.com/questions/1883071/how-do-i-find-the-data-directory-for-a-sql-server-instance