risposta-alla-domanda-sullo-sviluppo-web-bd.com

Come trovo la directory dei dati per un'istanza di SQL Server?

Abbiamo alcuni enormi database (20 GB +) che contengono principalmente dati di ricerca statici. Poiché la nostra applicazione esegue join su tabelle in questi database, devono essere parte di ogni SQL Server locale degli sviluppatori (ad esempio, non possono essere ospitati su un server di database condiviso centrale). 

Pianifichiamo di copiare un set canonico dei file di database di SQL Server (* .mdf e * .ldf) e li allega al database locale di ogni sviluppatore.

Qual è il modo migliore per trovare la directory dei dati dell'istanza locale di SQL Server in modo che possiamo copiare i file nel posto giusto? Questo sarà fatto tramite un processo automatico, quindi devo essere in grado di trovarlo e usarlo da uno script di compilazione.

65
Aaron Jensen

Dipende se il percorso predefinito è impostato per i file di dati e di registro o meno.

Se il percorso è impostato esplicitamente su Properties => Database Settings => Database default locations, il server SQL lo memorizza su Software\Microsoft\MSSQLServer\MSSQLServer nei valori DefaultData e DefaultLog.

Tuttavia, se questi parametri non sono impostati in modo esplicito, SQL Server utilizza i percorsi Dati e Log del database master.

Bellow è lo script che copre entrambi i casi. Questa è la versione semplificata della query eseguita da SQL Management Studio.

Inoltre, nota che utilizzo xp_instance_regread invece di xp_regread, quindi questo script funzionerà per qualsiasi istanza, default o 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

È possibile ottenere lo stesso risultato usando SMO. Qui sotto c'è l'esempio di C #, ma puoi usare qualsiasi altro linguaggio .NET o 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;
}

È molto più semplice in SQL Server 2012 e versioni successive, presupponendo di avere set di percorsi predefiniti (che è probabilmente sempre una cosa giusta da fare):

select 
    InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
    InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')
95
Alex Aza

Anche se questo è un thread molto vecchio, mi sento come se avessi bisogno di contribuire con una soluzione semplice ... Ogni volta che sai dove si trova un parametro di Management Studio a cui vuoi accedere per qualsiasi tipo di script automatico, il più semplice modo è quello di eseguire una traccia profiler veloce su un sistema di test standalone e catturare ciò che Management Studio sta facendo sul back-end.

In questo caso, supponendo che sei interessato a trovare i dati predefiniti e le posizioni dei registri, puoi fare quanto segue:

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

33
TDrudge

Mi sono imbattuto in questa soluzione nella documentazione per l'istruzione Crea Database nella guida di 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
32
Aaron Jensen

Per il database corrente puoi semplicemente usare:

select physical_name fromsys.database_files;

per specificare un altro database, ad es. 'Modello', usa sys.master_files

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

23
Remus Rusanu

A partire da SQL Server 2012, è possibile utilizzare la seguente query:

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

(Questo è stato preso da un commento su http://technet.Microsoft.com/en-us/library/ms174396.aspx , e testato.)

15
Nathan

Vari componenti di SQL Server (dati, log, SSAS, SSIS, ecc.) Hanno una directory predefinita. L'impostazione per questo può essere trovata nel registro. Leggi di più qui:

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

Quindi, se hai creato un database usando solo CREATE DATABASE MyDatabaseName, verrebbe creato nel percorso specificato in una delle impostazioni precedenti.

Ora, se l'amministratore/installatore ha cambiato il percorso predefinito, il percorso predefinito per l'istanza è archiviato nel registro in

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

Se si conosce il nome dell'istanza, è possibile interrogare il registro. Questo esempio è specifico per SQL 2008: fammi sapere se hai bisogno anche del percorso 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

Ogni database può essere creato sovrascrivendo le impostazioni del server in una propria posizione quando si rilascia l'istruzione CREATE DATABASE DBName con i parametri appropriati. Puoi scoprirlo eseguendo sp_helpdb

exec sp_helpdb 'DBName'
8
Raj More

Mantenere semplice:

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

questo restituirà tutti i database con i file associati

6
Deptor

Dalla GUI: apri le proprietà del tuo server, vai su Impostazioni database , e guarda Database posizioni predefinite .

Si noti che è possibile rilasciare i file del database in qualsiasi momento, anche se sembra più pulito tenerli nella directory predefinita.

2
Michael Petrotta

È possibile trovare i percorsi di dati e log predefiniti per l'istanza di SQL Server corrente utilizzando il seguente 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'
1
Regent

Espandendo la risposta "bit splattered", ecco uno script completo che lo fa:

@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
0
djangofan

avrei fatto un ripristino di backup semplicemente perché è più semplice e supporta il controllo delle versioni. In particolare, i dati di riferimento devono essere sottoposti a una versione per sapere quando ha iniziato ad avere effetto. Un allegato dettach non ti darà quell'abilità. Inoltre con i backup è possibile continuare a fornire copie aggiornate senza dover chiudere il database. 

0
Jayanth Kurup

La risposta di Alex è quella giusta, ma per i posteri ecco un'altra opzione: creare un nuovo database vuoto. Se si utilizza CREATE DATABASE senza specificare una directory di destinazione si ottengono ... le directory di dati/log predefiniti. Facile.

Personalmente però probabilmente lo farei:

  • RIPRISTINARE il database nel PC dello sviluppatore, anziché copiare/allegare (i backup possono essere compressi, esposti su UNC), oppure
  • Utilizzare un server collegato per evitare di farlo in primo luogo (dipende da quanti dati vanno oltre il join)

ps: 20 GB non è enorme, anche nel 2015. Ma è tutto relativo.

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

 enter image description here

È possibile scaricare lo script SQL dettagliato da come trovare la directory dei dati per un'istanza di SQL Server

0
frank tan

Piccola nitpick: non esiste una cartella dati, solo una cartella default data.

In ogni caso, per trovarlo, supponendo che si desidera installare per la prima istanza predefinita:

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

Se c'è un'istanza denominata, MSSQL.1 diventa qualcosa come MSSQL10.INSTANCENAME.

0
Stu