[SQL] Ermitteln der Pfade der SQL-Datenbanken

Gepostet am: May 12, 2014 9:48:3 AM

Um den lokalen Speicherpfad einer SQL-Instanz zu ermitteln, gibt es unterschiedliche Herangehensweisen:

Möglichkeit 1: mittels T-SQL

T-SQL

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

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

mein persönlicher Favorit [Quelle]

T-SQL alle Datenbanken mit Pfad auflisten

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

alle Datenbanken mit Pfad und Größe

Create Table ##temp( DatabaseName sysname, Name sysname, physical_name nvarchar(500), size decimal (18,2), FreeSpace decimal (18,2)) Exec sp_msforeachdb 'Use [?];Insert Into ##temp (DatabaseName, Name, physical_name, Size, FreeSpace) Select DB_NAME() AS [DatabaseName], Name, physical_name, Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) as nvarchar) Size, Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as decimal(18,2)) - Cast(FILEPROPERTY(name, ''SpaceUsed'') * 8.0/1024.0 as decimal(18,2)) as nvarchar) As FreeSpace From sys.database_files'Select * From ##tempdrop table ##temp

Möglichkeit 2: mittels Management Studio

Möglichkeit 3: SQL Server Configuration Manager

Möglichkeit 4: powershell

powershell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') "LOCALHOST"

$s.Settings.DefaultFile