Database Default Locations Not Persisting in SQL Server 2012

sql serversql-server-2012

When I set the default location for the Data & Log in the Server Properties in SQL Server 2012, and click Ok, it resets back to the previous value immediately – if I reopen the Server Properties, the values has been reset.

How do I get it to persist? I'm trying to set it to a location on a separate HDD. The Backup folder location has been changed without issue.

Best Answer

1) Make sure the account running the SQLservice should have the required rights on the directory you are wishing to change:

2) Also, you can give a try with below T-SQL

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

/* I am trying to change to D drive in below example*/

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\DATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\DATA'
GO
  1. Most important:-After Changing the Default Locations

You must stop and start the SQL Server service to complete the change

Related Question