Wir haben einige riesige Datenbanken (20 GB +), die meistens statische Nachschlagdaten enthalten. Da unsere Anwendung Joins für Tabellen in diesen Datenbanken ausführt, müssen sie Bestandteil des lokalen SQL-Servers der einzelnen Entwickler sein (d. H. Sie können nicht auf einem zentralen, gemeinsam genutzten Datenbankserver gehostet werden).
Wir planen, eine kanonische Gruppe der tatsächlichen SQL Server-Datenbankdateien (* .mdf und * .ldf) zu kopieren und an die lokale Datenbank jedes Entwicklers anzuhängen.
Was ist der beste Weg, um das Datenverzeichnis der lokalen SQL Server-Instanz herauszufinden, damit wir die Dateien an die richtige Stelle kopieren können? Dies geschieht über einen automatisierten Prozess, daher muss ich es in einem Build-Skript finden und verwenden können.
Es hängt davon ab, ob der Standardpfad für Daten- und Protokolldateien festgelegt ist oder nicht.
Wenn der Pfad explizit auf Properties
=> Database Settings
=> Database default locations
festgelegt ist, speichert SQL Server ihn unter Software\Microsoft\MSSQLServer\MSSQLServer
in DefaultData
und DefaultLog
.
Wenn diese Parameter jedoch nicht explizit festgelegt werden, verwendet der SQL Server Daten- und Protokollpfade der Master-Datenbank.
Bellow ist das Skript, das beide Fälle abdeckt. Dies ist eine vereinfachte Version der Abfrage, die SQL Management Studio ausführt.
Beachten Sie auch, dass ich xp_instance_regread
anstelle von xp_regread
verwende, sodass dieses Skript für jede Instanz (Standard oder benannt) funktioniert.
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
Sie können dasselbe Ergebnis erzielen, wenn Sie SMO verwenden. Bellow ist ein C # -Beispiel, Sie können jedoch jede andere .NET-Sprache oder PowerShell verwenden.
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 ist in SQL Server 2012 und höher so viel einfacher, vorausgesetzt, Sie haben Standardpfade festgelegt (was wahrscheinlich immer das Richtige ist):
select
InstanceDefaultDataPath = serverproperty('InstanceDefaultDataPath'),
InstanceDefaultLogPath = serverproperty('InstanceDefaultLogPath')
Auch wenn dies ein sehr alter Thread ist, habe ich das Gefühl, dass ich eine einfache Lösung beitragen muss. Jedes Mal, wenn Sie wissen, wo sich in Management Studio ein Parameter befindet, auf den Sie für automatisierte Skripts zugreifen möchten, ist dies am einfachsten Sie können einen schnellen Profiler-Trace auf einem eigenständigen Testsystem ausführen und die Aktivitäten von Management Studio im Backend erfassen.
Wenn Sie daran interessiert sind, die Standarddaten- und Protokollspeicherorte zu finden, können Sie in diesem Fall Folgendes tun:
WÄHLEN
SERVERPROPERTY ('instancedefaultdatapath') AS [DefaultFile],
SERVERPROPERTY ('instancedefaultlogpath') AS [DefaultLog]
Ich bin über diese Lösung in der Dokumentation zur Create Database-Anweisung in der Hilfe für SQL Server gestolpert:
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
Für die aktuelle Datenbank können Sie einfach Folgendes verwenden:
_select physical_name from
_ sys.database_files;
um eine andere Datenbank anzugeben, z. 'Model', benutze sys.master_files
select physical_name from sys.master_files where database_id = DB_ID(N'Model');
Ab SQL Server 2012 können Sie die folgende Abfrage verwenden:
SELECT SERVERPROPERTY('INSTANCEDEFAULTDATAPATH') as [Default_data_path], SERVERPROPERTY('INSTANCEDEFAULTLOGPATH') as [Default_log_path];
(Dies wurde einem Kommentar unter http://technet.Microsoft.com/de-de/library/ms174396.aspx entnommen und getestet.)
Verschiedene Komponenten von SQL Server (Daten, Protokolle, SSAS, SSIS usw.) verfügen über ein Standardverzeichnis. Die Einstellung dafür finden Sie in der Registry. Lesen Sie hier mehr:
http://technet.Microsoft.com/de-de/library/ms143547%28SQL.90%29.aspx
Wenn Sie also eine Datenbank nur mit CREATE DATABASE MyDatabaseName
erstellt haben, wird sie unter dem in einer der obigen Einstellungen angegebenen Pfad erstellt.
Wenn der Admin-Installer den Standardpfad geändert hat, wird der Standardpfad für die Instanz in der Registrierung unter gespeichert
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\[INSTANCENAME]\Setup
Wenn Sie den Namen der Instanz kennen, können Sie die Registrierung abfragen. Dieses Beispiel ist spezifisch für SQL 2008 - lassen Sie mich wissen, wenn Sie auch den SQL2005-Pfad benötigen.
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
Jede Datenbank kann erstellt werden, indem die Servereinstellung an einem eigenen Ort überschrieben wird, wenn Sie die Anweisung CREATE DATABASE DBName
mit den entsprechenden Parametern absetzen. Sie können das herausfinden, indem Sie sp_helpdb ausführen
exec sp_helpdb 'DBName'
Um es einfach zu halten:
use master
select DB.name, F.physical_name from sys.databases DB join sys.master_files F on DB.database_id=F.database_id
dadurch werden alle Datenbanken mit den zugehörigen Dateien zurückgegeben
Über die GUI: Öffnen Sie Ihre Servereigenschaften, wechseln Sie zu Database Settings und sehen Sie Database Standardverzeichnisse.
Beachten Sie, dass Sie Ihre Datenbankdateien an beliebiger Stelle ablegen können, obwohl es sauberer erscheint, sie im Standardverzeichnis zu behalten.
Sie können standardmäßige Daten- und Protokollspeicherorte für die aktuelle SQL Server-Instanz mithilfe des folgenden T-SQL ermitteln:
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'
Um die Antwort "Splattered Bits" zu erweitern, ist hier ein komplettes Skript, das es tut:
@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
ich hätte eine Backup-Wiederherstellung einfach gemacht, weil es einfacher ist und Versionierung unterstützt. Referenzdaten müssen insbesondere versioniert werden, um zu wissen, wann sie wirksam wurden. Ein Dettach-Attachment gibt Ihnen diese Fähigkeit nicht. Auch bei Sicherungen können Sie weiterhin aktualisierte Kopien bereitstellen, ohne die Datenbank herunterfahren zu müssen.
Alex 'Antwort ist die richtige, aber für die Nachwelt gibt es eine weitere Möglichkeit: Erstellen Sie eine neue leere Datenbank. Wenn Sie CREATE DATABASE verwenden, ohne ein Zielverzeichnis anzugeben, erhalten Sie die Standardverzeichnisse für Daten/Protokoll. Einfach.
Persönlich würde ich aber wahrscheinlich entweder:
ps: 20 gb sind auch 2015 nicht riesig. Aber alles relativ.
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\%'
Sie können ein detailliertes SQL-Skript von herunterladen, um das Datenverzeichnis für eine SQL Server-Instanz zu finden.
Kleiner Nitpick: Es gibt keinen Datenordner, nur einen default data-Ordner.
Um es zu finden, vorausgesetzt, Sie möchten die erste Standardinstanz installieren:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\SQLDataRoot
Wenn es eine benannte Instanz gibt, wird MSSQL.1 so etwas wie MSSQL10.INSTANCENAME.