Determining SQL data path for DB RESTORE with MOVE

The only viable solution I found is inspecting the registry from your T-SQL code:

DECLARE @filepath NVARCHAR(260)

EXEC master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', 
        N'DefaultData', 
        @filepath output, 'no_output' 

SELECT @filepath as 'Your default data directory'

I could have sworn that data path would be stored somewhere in a SERVERPROPERTY or a Dynamic Management View (DMV) - but no luck ......

Update: as @Mike pointed out - in SQL Server 2012 and newer, that information is available as a SERVERPROPERTY:

SELECT 
    DefaultDataPath = SERVERPROPERTY('InstanceDefaultDataPath'),
    DefaultLogPath = SERVERPROPERTY('InstanceDefaultLogPath')

Tags:

Sql

Sql Server

Tsql

Related