Step by Step Migration SQL Server database from Windows to Linux

SQL Server's backup and restore feature is the recommended way to migrate a database from SQL Server on Windows to SQL Server 2017 on Linux.

Prerequisites:

The following prerequisites are required to complete the migration.

Windows machine with the following:

• SQL Server installed.
• SQL Server Management Studio installed.

Target database to migrate.

• Linux machine with the following installed:
• SQL Server 2017 (RHEL, SLES, or Ubuntu) with command-line tools.

Create a backup on Windows Box:

There are several ways to create a backup file of a database on Windows. The following steps use SQL Server Management Studio (SSMS).

• Start SQL Server Management Studio on your Windows machine.
• In the connection dialog, enter localhost.
• In Object Explorer, expand Databases.
• Right-click your target database, select Tasks, and then click Back Up....
• In the Backup Up Database dialog, verify that Backup type is Full and Back up to is Disk. Note name and location of the file. For example, a database named DB on SQL Server 2016 has a default backup path of.

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB.bak

• Click OK to back up your database.

Another option is to run a Transact-SQL query to create the backup file. The following Transact-SQL command performs the same actions as the previous steps for a database called DB:

BACKUP DATABASE [DB] TO  DISK =

N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\DB.bak'

WITH NOFORMAT, NOINIT, NAME = N'DB-Full Database Backup',

SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

Copy the backup file to Linux:

To restore the database, you must first transfer the backup file from the Windows machine to the target Linux machine. There are several way to move the file to Linux from

• Use Bash shell (terminal window) running on Windows.(For this need to install a Bash shell on your Windows machine that supports the scp (secure copy) and ssh(remote login) commands.
• Then Open a Bash session on Windows. In your Bash session, navigate to the directory containing your backup file. 

cd 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\'

Then Use the scp command to transfer the file to the target Linux machine. The following example transfers DB.bak to the home directory of user1 on the Linux server with an IP address of 192.168.0.0:

scp DB.bak user@192.168.0.0:./

       OR

You can use ftp or graphically tools winscp for transforming backup file to your unix box.

Move the backup file to you backup directory before restoring on linux:

At this point, the backup file is on your Linux server in your user's home directory. Before restoring the database to SQL Server, you must place the backup in a subdirectory of /var/opt/mssql.

Connect to linux server:

ssh user@192.168.0.0

Enter super user mode.

sudo su

Create a new backup directory. The -p parameter does nothing if the directory already exists.

mkdir -p /var/opt/mssql/backup

Move the backup file to that directory. In the following example, the backup file resides in the home directory of user. Change the command to match the location and file name of your backup file.

mv /home/user/DB.bak /var/opt/mssql/backup/

Exit super user mode.

Exit

Restore your database on Linux:

To restore the database backup, you can use the RESTORE DATABASE Transact-SQL (TQL) command.

In the same terminal, launch sqlcmd. The following example connects to the local SQL Server instance with the SA user. Enter the password when prompted, or specify the password by adding the -P parameter.

sqlcmd -S localhost -U SA

At the sql>1 prompt, enter the following RESTORE DATABASE command, pressing ENTER after each line (you cannot copy and paste the entire multi-line command at once). Replace all occurrences of DB with the name of your database.

Sqlcmd>

RESTORE DATABASE DB

FROM DISK = '/var/opt/mssql/backup/DB.bak'

WITH MOVE 'YourDB' TO '/var/opt/mssql/data/DB.mdf',

MOVE 'YourDB_Log' TO '/var/opt/mssql/data/DB_Log.ldf'

GO

You should get a message the database is successfully restored.

Verify the restoration by listing all of the databases on the server. The restored database should be listed.

SELECT Name FROM sys.Databases

GO

Run other queries on your migrated database. The following command switches context to the DB database and selects rows from one of its tables.

USE DB

SELECT * FROM Table

GO

When you are done using sqlcmd, type exit.

Once complete your wok then exit with ssh session also.

==================Happy Learning================