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 WITH NOFORMAT, NOINIT, NAME = N'TestMigration-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
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
- We need to provide correct folder structure as in linux server. In this example I used /var/opt/mssql/data
- Providing correct logical file name as in the backup database
- 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.