SQL: Changing Default Installation Path for SQL Server

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

servprop1 SQL SERVER   Changing Default Installation Path for SQL Server

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.

servprop2 SQL SERVER   Changing Default Installation Path for SQL Server

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.

Advertisements

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