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:
- Backup the database from my Windows OS and restore it from Linux
- 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:
- Copy the database data and log files to /var/opt/mssql/data
- Run chmod 777 for the data and log file
- Connect to Sql Server using sqlcmd command line: sqlcmd -S localhost -U sa
- 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;
Test by querying your database table!