When you install SQL Server on Linux, the default instance file location is setup to ‘C:\var\opt\mssql\data\’ for data and log file.  Sometimes, it is necessary to relocate the physical files to different drives to get better throughput.

In this post, we shall walkthrough a step by step process of changing the default location of data or log directory on SQL Server on Linux system so that if there is a need of changing the files drive, you can easily achieve it by referring this blog.

Let’s connect to SQL Server Linux Instance using SSMS and go to instance property, select ‘Database Settings’ tab, and get the database default location.

You can also get this information using SERVERPROPERTY () function. From SQL Server 2016, there is an enhancement added to the SERVERPROPERTY () function. Using that function, you can check the default location of data and log file directories.

Syntax:

SELECT
SERVERPROPERTY(‘InstanceDefaultDataPath’) AS InstanceDefaultDataFilePath,
SERVERPROPERTY(‘InstanceDefaultLogPath’) AS InstanceDefaultLogFilePath

 

Change the default data or log directory

• Connect your Linux machine as a root user

Syntax: sudo -i

• Create the directories for data and log files where the new database’s files will reside. For example, I am going to create data file directory at /var/sqldata and log file directory at /var/sqllog locations.

   The below command will create both the folders in one attempt

Syntax: mkdir /var/sqldata  /var/sqllog

• Verify the created data  and log file folders

Syntax: ls -l

• The above step confirms that the directories got created. But the user and group of the folders are roots, let’s replace them to ‘mssql’ so that SQL Server service can access those folders.

   Change the user from root to mssql for the directories.

Syntax: chown mssql /var/sqldata  /var/sqllog

  Change the group from root to mssql for the directories.

Syntax: chgrp mssql /var/sqldata  /var/sqllog

• Use mssql-conf to change the default data and log directories with the “set” command:

   Replace the default location of data file with the new location

Syntax: /opt/mssql/bin/mssql-conf set defaultdatadir /var/sqldata

    Replace the default location of log file with the new location

Syntax: /opt/mssql/bin/mssql-conf set defaultlogdir /var/sqllog

• When you change the default location directory of the data file, log file or both the files,  you need to restart the SQL Server service to get the effect of new location(s)

Syntax: systemctl restart mssql-server

• Let’s verify the newly updated default location

   Via SSMS

 

   Via Function

Important Notes: 

• In CTP1 (public preview), when you change the default location of data directory and restart the SQL Server service, the tempdb database data and log files get created into the new location of the data directory.
• If you want to put tempdb data file and log files into two separate folders, you won’t be able to do that because changing the locations of TempDB data and log files is not supported in CTP1.
• System databases can not be moved with the mssql-conf utility

Hope, you enjoyed learning how to change the default data or log file directory location on SQL Server on Linux.