[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