How to restore a SQL Server database backup without knowing the destination path or filenames?

Question

I need to programmatically (T-SQL) restore a database backup in SQL Server 2008. This backup comes from a different server and the original database name may have been different as well. (In effect, I am copying a database from some server to a new database on another server.)

Apparently, I have to use RESTORE DATABASE ... WITH MOVE, which works, but I would need to know where the files should be restored to and how they should be named. Despite searching, I haven't found what would seem to be the most basic task: just use the default path and filenames, i.e., do whatever the SQL Server does when you issue a CREATE DATABASE - you don't have to specify the path for that command, so why is it needed for a RESTORE?

Have I overlooked some trivial solution, or will I really have to list the files, somehow find the database directory from the SQL Server configuration, and use that information to construct the RESTORE command? Thanks.

(Comparing with PostgreSQL, where when using pg_restore, you specify a destination database that is already created; and when you were creating it you had the option - not mandatory - of specifying a non-default tablespace, but if you didn't, you just don't care where the files are physically stored.)

Answer 1

For future Googlers; from SQL Server 2012 onwards you can use:

SELECT ServerProperty(N'InstanceDefaultDataPath') AS default_file
     , ServerProperty(N'InstanceDefaultLogPath') AS default_log
;

This reads the folder paths that appear under the Server Properties > Database Settings > Database Default Locations

Answer 2

I wrote a function a while ago which does this for you. It supports all versions of SQL Server including installations with default instance and named instances. See here for the code and an example

To do the restore you will call the function like shown below.

declare @sql nvarchar(2000), 
        @data varchar(260), 
        @log varchar(260); 

select @data = dbo.fn_get_default_path(0),
       @log = dbo.fn_get_default_path(1)

SELECT @sql= 'RESTORE DATABASE DefaultLocationDB 
FROM  DISK = N''c:\backups\DemoDB.bak'' WITH  FILE = 1,  
MOVE N''demo_data_device'' TO N''' + @data + '\DemoDb.mdf'',  
MOVE N''demo_log_device'' TO N''' +  @log + '\DemoDb.ldf'',  
NOUNLOAD, REPLACE'

exec (@sql)