How To Manage Database Files in SQL Server 2022 on Linux

By: Nirali Shastri   |   Updated: 2023-12-19   |   Comments   |   Related: > SQL Server on Linux

Problem

I am new to SQL Server 2022 on Linux and could use some guidance. What are the best practices for managing database files for user and system databases for SQL Server 2022 on Linux?

Solution

Recently, I was working on a project that involved moving database files to different locations. The SQL Server was installed on Ubuntu Linux, and we needed to move the database files to a separate directory. The Linux filesystem is entirely different from the Windows file system. In Linux, when you add a new partition, it must be mounted on a specific directory. So, the storage team added a new partition, and we started moving the database files. We also made some changes in the database file for uniform growth. Based on my experience, I am sharing a simple tip that might be helpful to the DBAs who are working on a similar project.

In this article, I will cover the following points:

1. Add and modify database files in an existing user database.
2. Move database files to another directory.

I have installed and configured the SQL Server on Ubuntu for this demonstration. You can read the brief guide to installing SQL Server on Linux. I have restored two databases named AdventureWorks2022 and Wideworldimportors.

Add and Modify SQL Server Database Files

The ALTER DATABASE ADD FILE statement is used to add a database file in an existing SQL Server database. Here is the syntax:

ALTER DATABASE [DATABASE_NAME] ADD FILE
(NAME=<logical_name>, FILENAME = <location_of_file>, SIZE = <file_size>, FILEGROWTH = <auto_growth>)
TO FILEGROUP [filegroup_name]

Code explanation:

1. database_name: SQL Server database name to add a secondary database file. Example: AdventureWorks2022.
2. logical_name: The logical file name uniquely identifies the database file name. Example: history_tables.
3. location_of_file: File path for the new database file. Example: /SQLDatabase/DataFiles/Adventureworks2022.
4. file_size: The initial database file size. Example: 512MB.
5. auto_growth: Database file auto-growth parameter. Example: 256MB.
6. filegroup_name: Filegroup name to add the database file. Example: the Primary filegroup.

The command to add a database file to a SQL Server database:

ALTER DATABASE [AdventureWorks2022] ADD FILE 
(
NAME = N'history_tables', FILENAME = N'/SQLDatabase/Datafiles/Adventureworks2022/history_tables.ndf',
SIZE = 524288KB , FILEGROWTH = 262144KB )
TO FILEGROUP [PRIMARY]

Once changes are completed, execute the following query to verify that the database file has been added:

use AdventureWorks2022
go
select name [File Name], Physical_name [File Location], State_desc [File State], convert(int,size*8)/1024 [File Size],  
convert(bigint,growth*8)/1024 [Database file growth]
from sys.database_files

The query output is below.

Now, we want to change the auto_growth value of the primary database file from 10% to a fixed size of 256 MB. To do that, we can use the ALTER DATABASE MODIFY FILE statement to change the autogrowth parameter of the existing database file. Here is the syntax:

ALTER DATABASE [database_name]
MODIFY FILE
(
    File_name = logical_file_name, FILEGROWTH = new_growth_value
)

Code explanation:

1. database_name: Database name for data file modification. Example: Adventureworks2022.
2. file_name: Filename autogrowth parameter to change. Example: AdventureWorks2022.
3. new_growth_value: Database file growth value. Example, 256MB.

The T-SQL script to modify the file:

USE [master]
GO
ALTER DATABASE [AdventureWorks2022] MODIFY FILE ( NAME = N'AdventureWorks2022', FILEGROWTH = 524288KB )
GO

Once the query executes successfully, verify that the changes are applied as expected. Here is the verification query:

use AdventureWorks2022
go
select name [File Name], Physical_name [File Location], State_desc [File State], convert(int,size*8)/1024 [File Size],  
convert(bigint,growth*8)/1024 [Database file growth]
from sys.database_files

The following image shows the query output:

As you can see, the auto growth parameter of the Adventureworks2022 file has been changed.

Move Database Files to Another Directory

Now, let’s learn how to move database files to another directory. Here are the steps:

1. Change the database state to a single user or make it offline.
2. Detach the database.
3. Move the database files to a new directory.
4. Attach the database.

Note: Moving database files requires downtime.

In this example, we will move the database files of the Wideworldimportors database. First, select the list of database files of the Wideworldimportors database by running the following query to give you the list of database files with their location.

use [WideWorldImporters]
go
select name [File Name], Physical_name [File Location], State_desc [File State], convert(int,size*8)/1024 [File Size],  
convert(bigint,growth*8)/1024 [Database file growth]
from sys.database_files

Here is the query output:

Now, once the list is ready, let’s begin the process.

Step 1: Change the State of the Database

Change the state of the database to a single user by executing the following command:

use master
go
alter database [WideWorldImporters] set single_user with rollback immediate
go

Step 2: Detach the Database

Detach the database by executing the sp_detach_db statement.

use master
go
exec sp_detach_db [WideWorldImporters]
go

Step 3: Move the Database Files to a New Directory

We can move database files using the mv command or directly from the operating system UI. Note: The user or group must have the required permissions (read, write, and execute) to the directory where we are moving the database files. In this demo, we are moving database files to the /SQLDatabase/DataFiles/Wideworldimportors directory; hence I have granted read, write, and execute permissions to all users on it.

The command to grant access is below:

chmod 777 /SQLDatabase/Datafiles/WideWorldImporters/

The command to move the database file is:

mv /var/opt/mssql/data/WideWorldImporters.mdf  /SQLDatabase/Datafiles/WideWorldImporters/
mv /var/opt/mssql/data/WideWorldImporters.ldf  /SQLDatabase/Datafiles/WideWorldImporters/
mv /var/opt/mssql/data/WideWorldImporters_UserData.ndf  /SQLDatabase/Datafiles/WideWorldImporters/

Step 4: Attach the database

The final step is to attach the database file. We can use CREATE DATABASE FOR ATTACH statement or sp_attach_db statement. Note: The sp_attach_db statement is deprecated; hence, it is advisable to use CREATE DATABASE FOR ATTACH statement. Below is the command.

USE [master]
GO
CREATE DATABASE [WideWorldImporters] ON 
( FILENAME = N'/SQLDatabase/Datafiles/WideWorldImporters/WideWorldImporters.mdf' ),
( FILENAME = N'/SQLDatabase/Datafiles/WideWorldImporters/WideWorldImporters.ldf' ),
( FILENAME = N'/SQLDatabase/Datafiles/WideWorldImporters/WideWorldImporters_UserData.ndf' )
 FOR ATTACH
GO

Once the database is attached, re-run the same query to verify that the database has been moved correctly.

use [WideWorldImporters]
go
select name [File Name], Physical_name [File Location], State_desc [File State], convert(int,size*8)/1024 [File Size],  
convert(bigint,growth*8)/1024 [Database file growth]
from sys.database_files

Here is the query output:

The above screenshot shows that the database files have been moved to the /SQLDatabase/Datafiles/WideWorldImporters directory.

Summary

In this tip, I have explained a step-by-step process to add a database file in an existing database, modify the configuration parameters of database files, and the process to migrate existing database files to a location in SQL Server on Linux.

Next Steps

1. Read ALTER DATABASE (Transact-SQL) to learn more about the ALTER DATABASE statement.
2. Read SQL Server on Linux Tips to learn more about SQL Server on Linux.

About the author

Nirali Shastri is an experienced Core Database Administrator having more than 4 years of experience managing large SQL Databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips