Determining SQL data path for DB RESTORE with MOVE

Question

I'm trying to write a script which automatically restores a database backup. I know I can use the following RESTORE command:

RESTORE DATABASE [DBRestoredName] 
FROM  DISK = N'C:\path\to\backup.bak' 
WITH  FILE = 1,  
MOVE N'DBNAME' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBNAME.mdf',  
MOVE N'DBNAME_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\DBNAME.ldf',  
NOUNLOAD

The problem with this is I want to be able to determine the SQL server's data location (i.e. the TO path) at run-time so the restored database is placed consistently alongside other databases on this server.

The database being restored won't exist on the server it's being restored to and I require the MOVE statements as the source server is likely to be SQL server 2005 and the target is 2008 therefore the file paths included in the backup file are not desirable.

So what ways could I determine the SQL data location programmatically?

Answer 1

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')

Answer 2

You can query this from the sys.database_files view in the database you wish to restore alongside physically. You will have to strip off the file name from the end of the physical_name column.

select * from sys.database_files