Migrating a SQL server database from Windows to Linux is not that difficult! The recommended method is a three-step process of 1) backing up the database to a file on the Windows machine, 2) moving that file over to the Linux machine and 3) using the restore feature to import the data in Linux. Each of these steps is very straightforward.
Backing Up Windows Data
If you’re using SQL Server Management Studio, making a backup is as simple as selecting it from the appropriate menu. Find your database in the Object Explorer, right click it, and select Tasks → Back Up Database. Use the backup menu to create a full database backup and save it to an appropriate location.
If you’re not using Management Studio, you’ll need to make the backup by using the TSQL BACKUP DATABASE command. The syntax is straightforward:
BACKUP DATABASE MyData TO DISK = 'Z:\WindowsBackups\MyData.bak' WITH FORMAT, MEDIANAME = 'Z_WindowsBackups', NAME = 'Windows Database';
In both cases, you should end up with a .bak file generated by your Windows database in a location that you control. The .bak file contains the entire contents of your database, so make sure you treat it securely!
Transferring the Backup to the Linux Server
Now that you’ve got the backup file, the next step is to get in on the Linux machine that’s running SQL Server. The standard way to achieve this is via scp, a tool for transferring files to remote machines over a network. While Windows doesn’t come with scp installed, you can install PuTTY and use it transfer the files.
Once you’ve got PuTTY installed and established a connection to the Linux server, the command to transfer the files is again very straightforward:
pscp Z:\WindowsBackups\MyData.bak user@linux_host:MyData.bak
Where “linux_host” is the address of your Linux server. The command stores the backup file in the home directory of the remote server, under the same name.
Next, using PuTTY, you’ll need to ssh into the remove server and move the file to a location where we can restore it from. With SQL Server for Linux, the restore file needs to be in a subdirectory of the main SQL Server directory of /var/opt/mssql. Once you’re ssh’d in, you can create the backup subdirectory:
mkdir -p /var/opt/mssql/backup
Then you can move the .bak file into that directory:
mv MyData.bak /var/opt/mssql/backup/MyData.bak
Restoring From the Backup File
Finally, to get the data onto the Linux SQL instance, you’ll need to use the .bak file to restore the data. Run the following command to get into a SQL shell:
sqlcmd -S localhost -U my_username
From there, all you need to do is run the RESTORE DATABASE command and pass in the filename you chose:
RESTORE DATABASE MyData FROM DISK = '/var/opt/mssql/backup/MyData.bak';
That’s it! From there, you should be able to query the Linux server exactly as you could the Microsoft one.