When you create a database in SQL Server using the "CREATE DATABASE" statement without any options, the database files will be created in some "default" locations.
Many people think this default location is the same as the location of the master database, but this is incorrect, it is possible to alter the location in server properties after installation of the server. (in Management Studio right click on the server and select "Server Properties", then go to the "Database Settings" tab and near the bottom you will see a section with "Database Default locations")
For example, suppose I execute the following statement:
On my machine this means 2 files will be created:
This is nice, you don't have to remember a path to pass, but when you want to restore a database from another server (where a different path is used) it can be a pain.
To restore a database with a specific location for the files we use the following syntax:
To make it easy to restore a database using a script I designed a function to figure out the location for the default Data path (and another one for the default log path).
The locations of these paths I got from http://msdn.microsoft.com/en-us/library/ms143547.aspx/
The output can be stored in a 260 character variable (260 as specified MAX_PATH in http://msdn.microsoft.com/en-us/library/aa365247.aspx).
Edit: Rudi Larno posted a very nice refactored version of the code I posted. (especially note the nice recursion in it.)
The functions can then be used as follows (or you can just copy paste the output):