How to change SQL Server 2016 Database default locations for data and log files

The best way to set the database default locations for data and logs is when you first install the SQL Server Instance. They can be set through the configuration.ini file.

; Default directory for the Database Engine user databases.

SQLUSERDBDIR="E:\MSSQLSERVER\MSSQL$MYINST\Data"

; Default directory for the Database Engine user database logs.

SQLUSERDBLOGDIR="F:\MSSQLSERVER\MSSQL$MYINST\Data"

But there are occasions where there’s a requirement to adjust the default locations after the SQL Server Instance is built. You can either write out the code , which requires references to xp_instance_regwrite or you can use the GUI to generate the code for you . This will save you time and also allow you to study how the code is constructed

The steps to take are :

a)Open SSMS and connect to the SQL Server Instance

b)Set the target paths in the Data & Log Fields .

c) To view the code generated click on the Script button and you’ll see code similar to the code below

 

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'E:\MSSQLSERVER\MSSQL$DBAMGT\Data'

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'F:\MSSQLSERVER\MSSQL$DBAMGT\Data'

GO