Skip to content

Migrate 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 on Linux. In this tutorial, you will walk through the steps required to move a database to Linux with backup and restore techniques.

    • Create a backup file on Windows with SSMS
    • Install a Bash shell on Windows
    • Move the backup file to Linux from the Bash shell
    • Restore the backup file on Linux with Transact-SQL
    • Run a query to verify the migration

    You can also create a SQL Server Always On Availability Group to migrate a SQL Server database from Windows to Linux. The following prerequisites are required to complete this tutorial:

    • 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 (RHEL, SLES, or Ubuntu) with command-line tools.

    Create a backup on Windows

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

    1. Start SQL Server Management Studio on your Windows machine.
    2. In the connection dialog, enter localhost.
    3. In Object Explorer, expand Databases.
    4. 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 YourDB on SQL Server 2016 has a default backup path of C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\YourDB.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 YourDB:

    SQL

    BACKUP DATABASE [YourDB] TO  DISK =

    N’C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\YourDB.bak’

    WITH NOFORMAT, NOINIT, NAME = N’YourDB-Full Database Backup’,

    SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    Install a Bash shell on Windows

    To restore the database, you must first transfer the backup file from the Windows machine to the target Linux machine. In this tutorial, we move the file to Linux from a Bash shell (terminal window) running on Windows.

    1. Install a Bash shell on your Windows machine that supports the scp (secure copy) and ssh (remote login) commands. Two examples include:
      1. The Windows Subsystem for Linux (Windows 10)
      1. The Git Bash Shell (https://git-scm.com/downloads)
    2. Open a Bash session on Windows.

    Copy the backup file to Linux

    1. In your Bash session, navigate to the directory containing your backup file. For example:

    Bash

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

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

    Bash

    scp YourDB.bak [email protected]:./

    There are alternatives to using scp for file transfer. One is to use Samba to configure an SMB network share between Windows and Linux. Once established, you can access it as a network file share from Windows, such as \\machinenameorip\share.

    Move the backup file before restoring

    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.

    1. In the same Windows Bash session, connect remotely to your target Linux machine with ssh. The following example connects to the Linux machine 192.0.2.9 as user user1.

    Bash

    ssh [email protected]

    You are now running commands on the remote Linux server.

    • Enter super user mode.

    Bash

    sudo su

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

    Bash

    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 user1. Change the command to match the location and file name of your backup file.

    Bash

    mv /home/user1/YourDB.bak /var/opt/mssql/backup/

    • Exit super user mode.

    Bash

    exit

    Restore your database on Linux

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

    1. 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.

    Bash

    sqlcmd -S localhost -U SA

    • At the >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 YourDB with the name of your database.

    SQL

    RESTORE DATABASE YourDB

    FROM DISK = ‘/var/opt/mssql/backup/YourDB.bak’

    WITH MOVE ‘YourDB’ TO ‘/var/opt/mssql/data/YourDB.mdf’,

    MOVE ‘YourDB_Log’ TO ‘/var/opt/mssql/data/YourDB_Log.ldf’

    GO

    You should get a message the database is successfully restored.

    RESTORE DATABASE may return an error like the following example:

    Bash

    File ‘YourDB_Product’ cannot be restored to ‘Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Product.ndf’. Use WITH MOVE to identify a valid location for the file.

    Msg 5133, Level 16, State 1, Server servername, Line 1

    Directory lookup for the file “Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Product.ndf” failed with the operating system error 2(The system cannot find the file specified.).

    In this case, the database contains secondary files. If these files are not specified in the MOVE clause of RESTORE DATABASE, the restore procedure will try to create them in the same path as the original server.

    You can list all files included in the backup:

    SQL

    RESTORE FILELISTONLY FROM DISK = ‘/var/opt/mssql/backup/YourDB.bak’

    GO

    You should get a list like the one below (listing only the two first columns):

    SQL

    LogicalName         PhysicalName                                                                 …………..

    ———————————————————————————————————————-

    YourDB              Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB.mdf          …………..

    YourDB_Product      Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Product.ndf  …………..

    YourDB_Customer     Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Customer.ndf …………..

    YourDB_log          Z:\Microsoft SQL Server\MSSQL11.GLOBAL\MSSQL\Data\YourDB\YourDB_Log.ldf      …………..

    You can use this list to create MOVE clauses for the additional files. In this example, the RESTORE DATABASE is:

    SQL

    RESTORE DATABASE YourDB

    FROM DISK = ‘/var/opt/mssql/backup/YourDB.bak’

    WITH MOVE ‘YourDB’ TO ‘/var/opt/mssql/data/YourDB.mdf’,

    MOVE ‘YourDB_Product’ TO ‘/var/opt/mssql/data/YourDB_Product.ndf’,

    MOVE ‘YourDB_Customer’ TO ‘/var/opt/mssql/data/YourDB_Customer.ndf’,

    MOVE ‘YourDB_Log’ TO ‘/var/opt/mssql/data/YourDB_Log.ldf’

    GO

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

    SQL

    SELECT Name FROM sys.Databases

    GO

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

    SQL

    USE YourDB

    SELECT * FROM YourTable

    GO

    • When you are done using sqlcmd, type exit.
    • When you are done working in the remote ssh session, type exit again.


    Migrate databases and structured data to SQL Server on Linux

    You can migrate your databases and data to SQL Server running on Linux. The method you choose to use depends on the source data and your specific scenario. The following sections provide best practices for various migration scenarios.

    Migrate from SQL Server on Windows

    If you want to migrate SQL Server databases on Windows to SQL Server on Linux, the recommended technique is to use SQL Server backup and restore.

    1. Create a backup of the database on the Windows machine.
    2. Transfer the backup file to the target SQL Server Linux machine.
    3. Restore the backup on the Linux machine.

    Migrate from other database servers

    You can migrate databases on other database systems to SQL Server on Linux. This includes Microsoft Access, DB2, MySQL, Oracle, and Sybase databases. In this scenario, use the SQL Server Management Assistant (SSMA) to automate the migration to SQL Server on Linux.

    Migrate structured data

    There are also techniques for importing raw data. You might have structured data files that were exported from other databases or data sources. In this case, you can use the bcp tool to bulk insert the data. Or you can run SQL Server Integration Services on Windows to import the data into a SQL Server database on Linux. SQL Server Integration Services enables you to run more complex transformations on the data during the import.