In this article, we will explore how to move SQL database files to another location in SQL Server 2019 on Linux. This can be useful when you need to change the storage location of your database files or when you want to optimize disk space usage.

Methods for Moving Database Files

There are two common methods for moving SQL database files:

1. Backup and restore the database
2. Detach and attach the database

Backup and Restore Method

The backup and restore method involves creating a backup of the database and then restoring it to the new location. Here are the steps to follow:

1. Create a backup of the database using the BACKUP DATABASE command.
2. Restore the database to the new location using the RESTORE DATABASE command.

Here is an example of how to restore the AdventureWorks2019 database to a new location:

USE [master]
GO
RESTORE DATABASE [AdventureWorks2019] FROM DISK = N'/var/opt/mssql/Backup/AdventureWorks2019.bak' WITH FILE = 1,
MOVE N'AdventureWorks2017' TO N'/MSSQL/SQLData/AdventureWorks2019.mdf',
MOVE N'AdventureWorks2017_log' TO N'/MSSQL/SQLLog/AdventureWorks2019_log.ldf',
NOUNLOAD, STATS = 5
GO

Detach and Attach Method

The detach and attach method involves detaching the database from the current location and then attaching it to the new location. Here are the steps to follow:

1. Detach the database using the sp_detach_db stored procedure.
2. Copy the database files to the new location using the cp command.
3. Attach the database using the CREATE DATABASE command.

Here is an example of how to detach and attach the WideWorldImporters database to a new location:

USE [master]
GO
ALTER DATABASE [WideWorldImporters] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC sp_detach_db [WideWorldImporters]
GO

After detaching the database, you can use the cp command to copy the database files to the new location:

cp /var/opt/mssql/data/WideWorldImporters.mdf /MSSQL/SQLData/
cp /var/opt/mssql/data/WideWorldImporters_UserData.ndf /MSSQL/SQLData/
cp /var/opt/mssql/data/WideWorldImporters.ldf /MSSQL/SQLLog/
cp -r /var/opt/mssql/data/WideWorldImporters_InMemory_Data_1/ /MSSQL/InMemoryData/

Finally, you can attach the database using the CREATE DATABASE command:

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

Conclusion

In this article, we have explored two methods for moving SQL database files in SQL Server 2019 on Linux. Whether you choose the backup and restore method or the detach and attach method, it is important to carefully follow the steps to ensure a successful move of your database files.