Migrate a SQL Server database from Windows to Linux using backup and restore

Applies to: SQL Server (all supported versions) — 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. See sql-server-linux-availability-group-cross-platform.

Prerequisites

The following prerequisites are required to complete this tutorial:

Windows machine with the following:

Linux machine with the following installed:

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

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 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:

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.

Install a Bash shell on your Windows machine that supports the scp (secure copy) and ssh (remote login) commands. Two examples include:

Open a Bash session on Windows.

Copy the backup file to Linux

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

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:

There are alternatives to using scp for file transfer. One is to use Samba to configure an SMB network share between Windows and Linux. For a walkthrough on Ubuntu, see How to Create a Network Share Via Samba. 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, as this is owned by the user mssql and group mssql . If you are looking to change the default backup location, see the Configure with mssql-conf article.

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.

You are now running commands on the remote Linux server.

Enter super user mode.

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

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.

Exit super user mode.

Restore your database on Linux

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

The following steps use the sqlcmd tool. If you haven’t install SQL Server Tools, see Install SQL Server command-line tools on Linux.

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.

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.

You should get a message the database is successfully restored.

RESTORE DATABASE may return an error like the following example:

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:

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

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

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

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

When you are done using sqlcmd, type exit .

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

Next steps

In this tutorial, you learned how to back up a database on Windows and move it to a Linux server running SQL Server. You learned how to:

• Use SSMS and Transact-SQL to create a backup file on Windows
• Install a Bash shell on Windows
• Use scp to move backup files from Windows to Linux
• Use ssh to remotely connect to your Linux machine
• Relocate the backup file to prepare for restore
• Use sqlcmd to run Transact-SQL commands
• Restore the database backup with the RESTORE DATABASE command
• Run the query to verify the migration

Next, explore other migration scenarios for SQL Server on Linux.

SQL Server on Linux Frequently Asked Questions (FAQ)

Applies to: SQL Server (all supported versions) — Linux

The following sections provide common questions and answers for SQL Server running on Linux.

General Questions

What Linux platforms are supported?

SQL Server is currently supported on Red Hat Enterprise Server, SUSE Linux Enterprise Server, and Ubuntu. It also supported running in a container with Docker. For the latest information about the supported versions, see Supported platforms.

Will SQL Server on Linux work on other platforms?

SQL Server is tested and supported on Linux for the previously listed distributions. Other Linux distributions are closely related and might be able to run SQL Server (for example, CentOS is closely related to Red Hat Enterprise Server). But if you choose to install SQL Server on an unsupported operating system, please review the Support policy section of the Technical support policy for Microsoft SQL Server to understand the support implications. Also note that some community-maintained Linux distributions do not have a formal way to receive support if the underlying operating system is the problem.

Is SQL Server on Linux the same as on Windows?

The core Database Engine for SQL Server is the same on Linux as it is on Windows. However, some features are currently not supported on Linux. For a list of features that are not supported on Linux, see the Unsupported features & services. Also review the Known issues. Unless specified in these lists, other SQL Server features and services are supported on Linux.

What is the support policy for SQL Server?

To understand the support policy, review the Technical Support Policy for SQL Server.

I am coming from a Windows SQL Server background. Are there resources to help learn how to use SQL Server on Linux?

The quickstarts provide step-by-step instructions on how to install SQL Server on Linux and run Transact-SQL queries. Other tutorials provide additional instructions on using SQL Server on Linux. For a third-party list of tips, see the MSSQLTIPS list of SQL Server on Linux Tips.

Licensing

How does licensing work on Linux?

SQL Server is licensed the same way for both Windows and Linux. In fact, you license SQL Server and then you can choose to use that license on the platform of your choice. For more information, see How to license SQL Server.

What edition of SQL Server should I choose when I already purchased it?

When you run mssql-conf setup you are presented with the following options:

If you have obtained your license through volume licensing as part of an Enterprise Agreement or through your MSDN subscription, you need to select options 4 through 7. This step does not ask you to enter the license, but you must have previously purchased the appropriate license for your configuration. If you have purchased Standard edition through a retail channel, select option 8. This option does prompt you to enter a key.

How do I verify the installed version and edition of SQL Server on Linux?

Connect to the SQL Server instance with a client tool such as sqlcmd, mssql-cli, or Visual Studio Code. Then run the following Transact-SQL query to verify the version and edition of SQL Server that you are running:

Installation

How do I get SQL Server installed on my Linux servers?

Microsoft maintains package repositories for installing SQL Server and supports installation via native package managers such as yum, zypper, and apt. To quickly install, see one of the quickstarts.

Can I install SQL Server on the Linux Subsystem for Windows 10?

No. Linux running on Windows 10 is currently not a supported platform for SQL Server and related tools.

Which Linux file systems can SQL Server use for data files?

Currently SQL Server on Linux supports ext4 and XFS. Support for other file systems will be added as needed in the future.

Can I download the installation packages to install SQL Server offline?

Yes. For more information, see the package download links in the Release notes. Also, review the instructions for offline installations.

Can I perform an unattended installation of SQL Server on Linux?

Yes. For a discussion of unattended installation, see Installation guidance for SQL Server on Linux. See the sample scripts for Red Hat, SUSE Linux Enterprise Server, and Ubuntu. You can also review this sample script created by the SQL Server Customer Advisory Team.

Tools

Can I use the SQL Server Management Studio client on Windows to access SQL Server on Linux?

Yes, you can use all your existing tools that run on Windows to access SQL Server on Linux. These include tools from Microsoft such as SQL Server Management Studio (SSMS), SQL Server Data Tools (SSDT), and OSS and third-party tools.

Is there a tool like SSMS that runs on Linux?

The new Azure Data Studio is a cross-platform tool for managing SQL Server. For more information, see What is Azure Data Studio.

Are commands like sqlcmd and bcp available on Linux?

Yes, sqlcmd and bcp are natively available on Linux, macOS, and Windows. In addition, use the new mssql-scripter command-line tool on Linux, macOS, or Windows to generate T-SQL scripts for your SQL database running anywhere. Also, see the preview release for mssql-cli.

Is it possible to view Activity Monitor when connected through SSMS on Windows for an instance running on Linux?

Yes, you can use SSMS on Windows to connect remotely, and use tools/ features such as Activity Monitor commands on a Linux instance.

What tools are available to monitor SQL Server performance on Linux?

You can use system dynamic management views (DMVs) to collect various types of information about SQL Server, including Linux process information. You can use Query Store to improve query performance. Other tools, such as the built-in Performance Dashboard, work remotely in SQL Server Management Studio (SSMS) from Windows.

One way to improve performance is to properly configure your Linux operating system and the SQL Server insance. For more information, see Performance best practices and configuration guidelines for SQL Server on Linux.

Administration

Has Microsoft created an app like the SQL Server Configuration Manager on Linux?

Yes, there is a configuration tool for SQL Server on Linux: mssql-conf.

Does SQL Server on Linux support multiple instances on the same host?

We recommend running multiple containers on a host to have multiple distinct instances. This is easily achieved using docker, but each container needs to listen on a different port. For more information, see Run multiple SQL Server containers.

Is Active Directory Authentication supported on Linux?

Are Always On and clustering supported in Linux?

Failover clustering and high availability on Linux are achieved with Pacemaker on Linux. For more information, see Business continuity and database recovery — SQL Server on Linux.

Is it possible to configure replication from Linux to Windows and vice versa?

Read-scale replicas can be used between Windows and Linux for one-way data replication.

Is it possible to migrate existing databases in older versions of SQL Server from Windows to Linux?

Yes, there are several methods of achieving this.

Can I migrate my data from Oracle and other database engines to SQL Server on Linux?

Yes. SSMA supports migration from several types of database engines: Microsoft Access, DB2, MySQL, Oracle, and SAP ASE (formerly SAP Sybase ASE). For an example of how to use SSMA, see Migrate an Oracle schema to SQL Server on Linux with the SQL Server Migration Assistant.

What permissions are required for SQL Server files?

All files in the /var/opt/mssql file folder should be owned by the mssql user and belong to the mssql group. Both the mssql user and group should have read-write permissions of all files and directories. Note the following special scenarios involving file and directory permissions:

• Permissions for mssql owner and group are required for mounted network shares that are used to store SQL Server files.
• If you locate database files or backups in a non-default directory, you must also set permissions for that directory.
• If you change the default root umask from 0022, SQL Server configuration fails after installation. You must then manually apply required permissions to SQL Server startup account.

Can I change the ownership of SQL Server files and directories from the installed mssql account and group?

We do not support changing the ownership of SQL Server directory and files from the default installation. The mssql account and group is specifically used for SQL Server and has no interactive login access.

Is Symbolic links supported for SQL Server data and log directories?

No, symbolic links are not supported for SQL Server data and log directories. To change the default data and log directories, see Change the default data or log directory location.