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

12 October,2017 by Jack Vamvas

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

Database_settings

 

 

 

 

 

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


 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to change SQL Server 2016 Database default locations for data and log files


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer