SSMS – CREATE/RESTORE Database wizard – Change the default database file path

Today’s post takes me back to the days when I was learning SQL Server at the engineering school about 8 years ago. I learnt SQL 7 and SQL 2000 and then as I joined my present employer, SQL 2005 came along. While SQL 7/2000 had the Enterprise Manager, SQL 2005 onwards we have the SSMS – SQL Server Management Studio. Irrespective of the tool, each of these had wizards for Creating and Restoring a database.

Whenever we create or restore a database, we can see that the SSMS fills in a default path for placing the data and log files. This path is set during the Server installation, and is normally set to %PROGRAMFILES%Microsoft SQL ServerMSSQL11.(InstanceName)MSSQLDATA on a SQL 11 (Code Named: “Denali”) CTP 01 instance (Previous releases of SQL Server also follow a similar path).

Generally, I prefer to place the data and log files on a different physical drives whenever possible. I won’t go into the details of why I do this right now, but it is important to know that for most cases, it is a recommended best practice. It becomes an annoyance to change the data and log file paths every time I need to create or restore a database. Hence, I typically change the default file paths at the server instance level.

Here’s how you can do it yourself:

Launch the SQL Server Management Studio
Open the Object Explorer by going to View –> Object Explorer and connect to the SQL Server instance under consideration
Right-click the instance name and click on “Properties”
In the server properties window, navigate out to the Database Properties tab
Change the Data & Log file paths under the “Database Default locations” section

Once changed, you can then see that these changes are used by default by the Create Database and Restore Database wizards. On my test instance, I don’t have multiple drives to spread the data/logs across, hence I have added the default locations as E:DatabasesData for the data files and E:DatabasesLogs for the Log Files.