Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Change Default Data and Log file directory for SQL Server running on Linux


By:   |   Read Comments   |   Related Tips: More > SQL Server on Linux

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

I have written several tips on SQL Server vNext running on Linux and in this tip I will explain how to change the default data and log file directory of SQL Server databases running on Linux servers.

Solution

The process to configure SQL Server running on Linux has changed from SQL Server running Windows where we use SQL Server Configuration Manager to manage changes in TCP ports, SQL Server services, etc. Changing the default database file location for SQL Server running on Linux is also one of those activities whose process is different as compared to Windows based SQL Servers. Microsoft has provided a configuration script called "mssql-conf" for Linux based SQL Server to make such changes. The "mssql-conf" script installs with SQL Server vNext for both Red Hat Enterprise Linux and Ubuntu. This utility is very useful to set the parameters like TCP ports, default data file location, default log file location, etc.

This tip is intended to focus on only changing the default database and log file location. Stay tuned for more tips focusing on different activities using this same configuration script.

Change the Default SQL Server Database File Location in Linux

Before going ahead, you need to have SQL Server vNext running on Red Hat Linux or an Ubuntu server.

Step 1: First, I would recommend running a full backup of all your user and system databases in case anything goes wrong with the existing database files and you need to restore all your databases.  As SQL Server vNext is a CTP edition, I assume you will not have any business critical applications running, but recreating a test environment still takes time.

Step 2: Now connect to the Linux server using PuTTY or login to the server locally. I connected to my Linux server where SQL Server vNext is running through PuTTY as shown below.

connect to Linux Server

Step 3: The current default location of SQL Server database files is /var/opt/mssql/data which is set during the SQL Server installation. We can check the database files under this location to validate the path. Run ls -lrt /var/opt/mssql/data to display all files in this location.

#use sudo to display files under mssql folder. Enter password once you get prompt.
sudo su

#Display files of location /var/opt/mssql/data
ls -lrt /var/opt/mssql/data

We can see all database files are in this location /var/opt/mssql/data.

ls -lrt /var/opt/mssql/data

We can also verify the directory by creating a dummy database without mentioning the file location and then check the database file location of this newly created database. Connect to SQL Server using sqlcmd, create a database called MSSQLTIPS and then check the database file location of the database.

#Connect to SQL Server. Enter the sa password once you get password prompt.
sqlcmd -S192.168.209.128 -Usa

#Create database MSSQLTIPS
CREATE DATABASE MSSQLTIPS
GO

#Change database context to MSSQLTIPS to execute queries on this database.
USE MSSQLTIPS
GO

#Check database file location of database MSSQLTIPS.
SELECT filename from sysfiles
GO

Once we execute the above commands, we can get the location of the new MSSQLTIPS database as shown below. We can see that the location of the database files of this newly created database is /var/opt/mssql/data which is same location as shown in the above screenshot.

check database file location post creating a db mssqltips

Step 4: Now we have to change this database file location to somewhere else let's say from /var/opt/mssql/data to /opt/mssql/data folder, so that whenever a new database is created, the database files would go to this new location (opt/mssql/data) not the old location (/var/opt/mssql/data). As the 'data' folder is not there i.e. /opt/mssql/ first we will create this directory then we will assign the appropriate rights to the directory so that SQL Server can access this folder.

#create a directory named data under /opt/mssql/
mkdir /opt/mssql/data

Once you execute the above command, the 'data' directory will be created as shown in the image below. We also executed ls -lrt for both locations to compare the files. We can see there are no files in the /opt/mssql/data directory because it is newly created whereas we can see all database files placed under the /var/opt/mssql/data directory. If you notice you can see the database files of the newly created database MSSQLTIPS in the same location.

create the directory data under /opt/mssql/

Now we will change the owner and group of this folder, so that SQL Server can access this directory. Run the below command to change the owner of this directory.

#Run below command to change the owner and group of this directory to user "mssql"
sudo chown mssql /opt/mssql/data
sudo chgrp mssql /opt/mssql/data

change owner and group of directory to mssql

Step 5: Now we will use the configuration script "mssql-conf" to set the default database file location. This script installs in the bin directory with the SQL Server vNext installation. We can validate this before executing this command. Run ls -lrt /opt/mssql/bin to display files in the bin folder.

check mssql-conf file

We can see configuration script "mssql-conf" is there in the bin folder from the above image, so now execute this script through the below command to change the default database file location to /opt/mssql/data.

#execute below command to change the default database file location.
sudo /opt/mssql/bin/mssql-conf set defaultdatadir /opt/mssql/data

set default data file location

Once you execute the above command, it will set the default data file location as per your choice asking you to restart the SQL Server service to apply this change on SQL Server. Run the below command to restart the SQL Server service.

#Restart SQL Server Service
systemctl restart mssql-server
restart mssql-server

Step 6: Now we will connect to SQL Server using sqlcmd to validate the above change which we made. Run the sqlcmd utility to make a database connection to the Linux server. Here we are getting an issue when establishing a database connection.

Error during making a database connection

This is known issue and Microsoft has already suggested a fix to this issue in the online documentation. The fix is to move the tempdb data and log file to the new location /opt/mssql/data manually. I executed the below command to move both files to the target location.

#Move tempdb data file tampdb.mdf to /opt/mssql/data
sudo mv /var/opt/mssql/data/tempdb.mdf /opt/mssql/data

#Move tempdb log file tamplog.ldf to /opt/mssql/data
sudo mv /var/opt/mssql/data/templog.mdf /opt/mssql/data

Move tempdb database files to /opt/mssql/data

Once the tempdb data and log files have been copied to the new default database file location, connect to SQL Server using sqlcmd utility and this time I have successfully established a database connection. Here we can check the database file location of TempDB to verify the changes. We can see now TempDB is online from the /opt/mssql/data folder whereas earlier it was from the /var/opt/mssql/data which was shown in the initial screenshots.

check tempdb file location

Step 7: Now we will create a new database named "Test" without mentioning the database file location and we will validate where the files are located.  We can see the newly created database "Test" has both database files in the /opt/mssql/data directory which is our target location.

create test db and check file location

You can also check the directory /opt/mssql/data to get the files placed there by running the ls -lrt command. Below is the screenshot of this command and we can see the database files. At this point we can confirm the default database file location has been set for your instance to /opt/mssql/data.

check tempdb file location

Change the Default SQL Server Transaction Log File Location in Linux

Step 1: If you want to place your data file and log file in different directories then we need to set the default log file location as well. Let's assume we need to set our default data directory to /opt/mssql/data/ and the default log directory to /opt/mssql/log/. We have already changed the default data file location of SQL Server in the above section, so now we will set the default log file location in this section. Create another directory named log under /opt/mssql path as it does not exist.

#Create directory log under /opt/mssql
mkdir /opt/mssql/log

#Check directores under /opt/mssql
ls -lrt /opt/mssql/

mkdir /opt/mssql/log

We can see both data and log directories are there in this location in the above screenshot. Next we will change the owner and group of this directory to the mssql user as we did in the above section.

Step 2: We will run the below command to change the owner and group of this directory to the "mssql" user as we did in the section above.

#Change owner
sudo chown mssql /opt/mssql/log

#Change group
sudo chgrp mssql /opt/mssql/log

change owner and group to mssql user

Step 3: Now we will execute the "mssql-conf" configuration script to set the default log file location of the SQL Server instance. Once you execute the below command it will set the new value for the default transaction log file location and ask you to restart the SQL Server service which we will do next.

#Change default log file location
sudo /opt/mssql/bin/mssql-conf set defaultlogdir /opt/mssql/log

set default log file location

Restarting the SQL Server service as suggested in the above screenshot by running the below command.

#Restart SQL Server service
systemctl restart mssql-server

Step 4: Now we have set the default log file location of the SQL Server instance, so whenever we create a new database the data file should go to the /opt/mssql/data directory as we set in this configuration in the first section and the log file should go to the /opt/mssql/log directory as we set this configuration in this section. Let's validate this change by making a database connection to SQL Server, create a new database named "Test_Log" and then check the database file location of this database. Run the commands below.

#Connect to SQL Server. Enter the sa password once get password prompt.
sqlcmd -S192.168.209.128 -Usa

#Create New Database Test_Log
CREATE DATABASE Test_Log
GO

#Change database context to Test_Log
USE Test_Log
GO

#Check database file location of Test_Log
Select filename from sysfiles

We can see the data and log file location of the newly created database "Test_Log" and the directories are as expected.

check default log file location
Next Steps
  • The configurational script "mssql-conf" is very useful and you should explore more about this. I will write more tips on this utility in coming days.
  • Learn more about SQL Server vNext version and stay tuned for more tips on this topic.
  • Explore more knowledge on SQL Server Database Administration Tips.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools