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:

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

image

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.

image

image

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s