Attach SQL Sever Database Running in Linux

Microsoft had made SQL Server 2017 to be able to running in Linux machine. That is a very good thing. I had it installed and running fine. But after that how can I move my Windows database to Linux? Since I am dealing with a legacy application, the migration files are messed up. It might be because dotnetcore migration also still have many bugs in it. So my only options are:

  1. Backup the database from my Windows OS and restore it from Linux
  2. Copy the database file to Linux and create a new database and attach the file

I tried the options 1, it failed, SQL Server still trying to find files from Windows file path. So I left with option 2 only.

After trying many times I finally made it. These are the steps:

  1. Copy the database data and log files to /var/opt/mssql/data
  2. Run chmod 777 for the data and log file
  3. Connect to Sql Server using sqlcmd command line: sqlcmd -S localhost -U sa
  4. Run the attach T-SQL script:

CREATE DATABASE [db_name] ON PRIMARY (FILENAME=N=’/var/opt/mssql/data/your_db_Data.mdf’) LOG ON (FILENAME=N’/var/opt/mssql/data/your_db_Log.ldf’) FOR ATTACH;

GO

Test by querying your database table!