[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