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 (https://blog.sqlauthority.com)
15 Comments. Leave new
super …
Very nice… easy way to change path…..
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?
Dear, I have the same problem, find a solution to the problem?
Thanks for your help
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 –
Changes to backup/restore wizard:
good info…….
Good Info….
How to get the same information through query, please help
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?
Just use DETACH, then move the physical files (.mdf and .ldf) then use ATTACH to reattach the database from its new location.
hai
In my machine i have sql server 2005 in C drive,now i want chage that total Instance into D drive.Anyone plz tell me how to change sql server 2005 instance “Root directory” from C drive to D drive.
You can detach and attach the database from C to D.
For complete movement, you need to uninstall and reinstall SQL.
I’m trying to install 2014 Standard edition (64bit) and dont seem able to prevent it from installing most of the application on the c drive – which runs out of space during the installation, how can i force SQL to install everything to the d drive?
you can’t force EVERYTHING on d drive because C is system drive. Unless you make d drive as system drive, it won’t be possible.
I have a server where SQL Server 2017 has been uninstalled and reinstalled. The first time I installed (SQL Server Express 2017, upgraded to Standard later), the database instance root was “D:\Program Files\Microsoft SQL Server\”. The new install is only letting me specify “C:\Program Files\Microsoft SQL Server\”; it is grayed out and cannot be modified.
How can I resolve this short of reloading the entire server?