Migrate SQL Server database on Windows to SQL Server on Linux(RedHat 7)

After SQL Server VNext CTP release on Linux, one of the usecase is to migrate SQL Server database on Windows Server to SQL Server on Linux. This blog is to explain steps on migrating SQL Server windows to Linux.

Source Server Version : Windows 10, SQL Server 2016
Destination Server Version : RedHat Linux 7.3, SQL Server VNext (2017) CTP 2.1

SQL Server Backup and Restore method is one of the easiest and better way to migrate from Windows to Linux.

Backup from Windows

We dont need any additional steps for taking backup for Linux. Basic TSQL Backup command is good enough as below:

BACKUP DATABASE [TestMigration] 
	TO  DISK = N'C:\VirtualMachines\VMShare\TestMigration.bak'  --Provide your path to take backupname
	NAME = N'TestMigration-Full Database Backup', SKIP, 

After successful execution of backup script, we need to move this backup file to linux server. I used ftp to move windows database backup to Linux server as below. After moving to ftp share, we can access ftp share from linux as below

$ ftp ipaddress
ftp> get TestMigration.bak
Restoring Database in Linux

Restore TSQL command is the same approach to restore database into Linux Server

RESTORE DATABASE [TestMigration] FROM  DISK = N'/var/opt/mssql/data/TestMigration.bak' 
	WITH MOVE 'TestMigration' TO '/var/opt/mssql/data/TestMigration.mdf',
	MOVE 'TestMigration_Log' TO '/var/opt/mssql/data/TestMigration_Log.ldf'
Points to consider
  1. We need to provide correct folder structure as in linux server. In this example I used /var/opt/mssql/data
  2. Providing correct logical file name as in the backup database
  3. We can execute this command by using SQLCMD in Linux or connecting SQL Server on Linux on SQL Server Management Studio or Visual Studio Code.

These simple steps are enough to migrate a windows SQL Server database to Linux.