valida - ¿Cómo encuentro el directorio de datos para una instancia de SQL Server?

script tamaño base de datos sql server

Question

Tenemos algunas bases de datos enormes (20GB +) que en su mayoría contienen datos de búsqueda estáticos. Debido a que nuestra aplicación ejecuta uniones contra tablas en estas bases de datos, tienen que ser parte de cada servidor SQL local de los desarrolladores (es decir, no pueden alojarse en un servidor central de base de datos compartida).

Planeamos copiar un conjunto canónico de los archivos de base de datos de SQL Server (* .mdf y * .ldf) y adjuntarlos a la base de datos local de cada desarrollador.

¿Cuál es la mejor manera de averiguar el directorio de datos de la instancia local de SQL Server para que podamos copiar los archivos en el lugar correcto? Esto se hará a través de un proceso automatizado, así que tengo que ser capaz de encontrarlo y usarlo desde un script de compilación.

Answer 1

A partir de Sql Server 2012, puede usar la siguiente consulta:

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

(Esto fue tomado de un comentario en http://technet.microsoft.com/en-us/library/ms174396.aspx , y probado).

Answer 2

Ampliando la respuesta de "bits salpicados", aquí hay una secuencia de comandos completa que lo hace:

@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

Answer 3

Aunque este es un hilo muy antiguo, siento que debo contribuir con una solución simple. Cada vez que sepa dónde se encuentra en Management Studio un parámetro al que desea acceder para cualquier tipo de script automatizado, la forma más fácil es ejecutar un rastreo de perfil rápido en un sistema de prueba independiente y capturar lo que Management Studio está haciendo en el back-end .

En este caso, suponiendo que esté interesado en encontrar los datos predeterminados y las ubicaciones de registro, puede hacer lo siguiente:

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

Answer 4

Depende de si la ruta predeterminada está configurada para archivos de datos y de registro o no.

Si la ruta se establece explícitamente en Properties => Database Settings => Database default locations , SQL Server la almacena en Software/Microsoft/MSSQLServer/MSSQLServer en los valores DefaultData y DefaultLog .

Sin embargo, si estos parámetros no se establecen explícitamente, el servidor SQL usa las rutas de datos y registros de la base de datos maestra.

Bellow es el guión que cubre ambos casos. Esta es una versión simplificada de la consulta que ejecuta SQL Management Studio.

Además, tenga en cuenta que uso xp_instance_regread lugar de xp_regread , por lo que este script funcionará para cualquier instancia, predeterminada o con nombre.

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

Puede lograr el mismo resultado utilizando SMO. Bellow es una muestra de C #, pero puede usar cualquier otro lenguaje .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;
}

Es mucho más simple en SQL Server 2012 y superior, suponiendo que tiene un conjunto de rutas predeterminadas (que probablemente siempre sea lo correcto):

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

Answer 5

Desde la GUI: abra las propiedades de su servidor, vaya a Configuración de la base de datos y vea las ubicaciones predeterminadas de la base de datos .

Tenga en cuenta que puede soltar los archivos de su base de datos donde lo desee, aunque parece más limpio mantenerlos en el directorio predeterminado.

Answer 6

Hubiera hecho una restauración de copia de seguridad simplemente porque es más fácil y compatible con el control de versiones. Los datos de referencia especialmente necesitan ser versionados para saber cuándo comenzaron a tener efecto. Una conexión dettach no te dará esa habilidad. También con copias de seguridad puede continuar proporcionando copias actualizadas sin tener que cerrar la base de datos.

Answer 7

Manteniéndolo 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

esto devolverá todas las bases de datos con archivos asociados

Answer 8

Me encontré con esta solución en la documentación de la declaración Create Database en la ayuda para 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

Answer 9

Obtendrá la ubicación predeterminada si la base de datos de usuario mediante esta consulta:

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

Answer 10

Para la base de datos actual, puede usar:

select physical_name from sys.database_files;

para especificar otra base de datos, por ejemplo, ''Modelo'', use sys.master_files

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

Answer 11

Pequeño nitpick: no hay una carpeta de datos, solo una carpeta de datos predeterminada .

De todos modos, para encontrarlo, suponiendo que desea instalar para la primera instancia predeterminada:

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

Si hay una instancia con nombre, MSSQL.1 se convierte en algo así como MSSQL10.INSTANCENAME.

Answer 12

Puede encontrar ubicaciones predeterminadas de Datos y Log para la instancia actual de SQL Server utilizando el siguiente 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''

Answer 13

Varios componentes de SQL Server (Datos, Registros, SSAS, SSIS, etc.) tienen un directorio predeterminado. La configuración para esto se puede encontrar en el registro. Leer más aquí:

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

Por lo tanto, si creó una base de datos utilizando simplemente CREATE DATABASE MyDatabaseName , se crearía en la ruta especificada en una de las configuraciones anteriores.

Ahora, si el administrador / instalador cambió la ruta predeterminada, entonces la ruta predeterminada para la instancia se almacena en el registro en

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

Si conoce el nombre de la instancia, puede consultar el registro. Este ejemplo es específico de SQL 2008; avíseme si también necesita la ruta 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

Cada base de datos se puede crear anulando la configuración del servidor en su propia ubicación cuando se emite la instrucción CREATE DATABASE DBName con los parámetros apropiados. Puede averiguarlo ejecutando sp_helpdb

exec sp_helpdb ''DBName''

Answer 14

share es la correcta, pero para la posteridad, aquí hay otra opción: crear una nueva base de datos vacía. Si utiliza CREATE DATABASE sin especificar un directorio de destino obtendrá ... los directorios de datos / registro predeterminados. Fácil.

Personalmente, sin embargo, probablemente yo también:

• RESTAURAR la base de datos a la PC del desarrollador, en lugar de copiar / adjuntar (las copias de seguridad se pueden comprimir, exponer en un UNC) o
• Utilice un servidor vinculado para evitar hacer esto en primer lugar (depende de la cantidad de datos que pasa por la unión)

ps: 20 gb no es enorme, incluso en 2015. Pero todo es relativo.

Answer 15

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

Puede descargar secuencias de comandos SQL detalladas de cómo encontrar el directorio de datos para una instancia de SQL Server