title: How to restore a SQL Server database backup without knowing the destination path or filenames?
body: <p>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.)</p>
  
  <p>Apparently, I have to use <code>RESTORE DATABASE ... WITH MOVE</code>, 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 <code>CREATE DATABASE</code> - you don't have to specify the path for that command, so why is it needed for a <code>RESTORE</code>?</p>
  
  <p>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 <code>RESTORE</code> command? Thanks.</p>
  
  <p>(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.)</p>