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

sql-server sql-server-2008 tsql database-restore
user1224797 picture user1224797 · Feb 22, 2012 · Viewed 12.7k times · Source

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

gvee picture gvee · May 3, 2016

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

Server Properties > Database Settings

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More