SQL SERVER – Changing Default Installation Path for SQL Server

Earlier I wrote a blog post about SQL SERVER – Move Database Files MDF and LDF to Another Location and in the blog post we discussed how we can change the location of the MDF and LDF files after database is already created. I had mentioned that we will discuss how to change the default location of the database. This way we do not have to change the location of the database after it is created at different locations.

The ideal scenario would be to specify this default location of the database files when SQL Server Installation was performed. If you have already installed SQL Server there is an easy way to solve this problem. This will not impact any database created before the change, it will only affect the default location of the database created after the change.

To change the default location of the SQL Server Installation follow the steps mentioned below:

Go to Right Click on Servers >> Click on Properties >> Go to the Database Settings screen

You can change the default location of the database files. All the future database created after the setting is changed will go to this new location.

You can also do the same with T-SQL and here is the T-SQL code to do the same.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'F:\DATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'F:\DATA'
GO

What are the best practices do you follow with regards to default file location for your database? I am interested to know them.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

10 thoughts on “SQL SERVER – Changing Default Installation Path for SQL Server

  1. Good overview, but unfortunately you haven’t tried to set the DB and Log location to an other than the system drive. In our situation I’ve configured the DB and Log location during the setup to point to S:\MSSQL\DATA and L:\MSSQL\LOGS respectively. Directories are indeed created, but when I put a mdf and ldf in the correct directory and want to attache that DB, I can only brows the c:\ drive.
    The MSSQLSERVER account and the account the SQL service is running with do have “Full control” on the S and L drive.
    I’ve not seen any post about others having the same probelem. Any suggestions?

  2. Here’s an interesting little change that Microsoft made to the Backup/Restore wizard with respect to default file naming (it’s not related to default file path, but since we are relating to the backup/restore wizard in this post, I thought of adding my 2 cents :))

    MS Connect Case #668566 – http://beyondrelational.com/modules/2/blogs/77/posts/11327/ssms-database-restore-physical-file-names-should-default-to-logical-file-names-ms-connect-case-66856.aspx

    Changes to backup/restore wizard: http://beyondrelational.com/modules/2/blogs/77/Posts/14247/0153-sql-server-changes-to-restore-database-wizard-in-sql-2012-quotdenaliquot.aspx

  3. Thanks for this article. It seems to imply that all FUTURE databases created after this setting will go to this new location. However I want to move all the existing databases to this new location as I am running out of disk space on the C drive partition. Do you know how this can be done? Can I simply copy everything over to the new location?

Leave a Reply

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