How to Copy SQL Server Database from Windows to Linux

After Microsoft has launched SQL Server for Linux, even if your main tech stack is tied to Windows Server you may need to copy the database to another SQL Server located on a Linux computer.

For example, this can come in handy if you want to provide developers with a database for testing without buying a Windows Server license. 

There are a few ways to tackle this task. This article examines the following methods to copy SQL Server database from Windows to Linux:

  1. Using T-SQL’s BACKUP DATABASE and RESTORE DATABASE commands;
  2. Generation of T-SQL database script (such as mysqldump);
  3. Creating BACPAC;
  4. SqlBak service.

Below you will find a summary of each method.

Using T-SQL’s BACKUP DATABASE and RESTORE DATABASE commands

The first thing that comes to mind is creating a native backup. And its subsequent restoration to SQL Server located on the Linux server. 

First, run BACKUP DATABASE command on Windows server:

BACKUP DATABASE [AdventureWorks] TO  DISK = 'c:\Temp\AdventureWorks.bak'

We also need to know the logical names of database files to restore a database on a Linux server. These can be viewed in SSMS by right-clicking on the database and selecting Properties.

And then in the window that appears, select Files, and Logical Name column will contain a list of database file names.

You can also get the database file names through the following T-SQL query:

SELECT f.name LogicalName, f.type_desc TypeOfFile  FROM [sys].[master_files] f  INNER JOIN [sys].[databases] d ON d.database_id = f.database_id  where d.name = 'AdventureWorks'

After you have made a backup and found out the database file names, it’s time to restore it on Linux.

First, you need to copy the backup file to the Linux server, and then you can restore the database.

You will need to specify the path in RESTORE DATABASE command not only to the location of the copied backup but also to the files in which the database will be physically stored. We learned the names of these files during the previous step.

RESTORE DATABASE [AdventureWorks] FROM DISK = '/home/username/AdventureWorks.bak' WITH   MOVE 'AdventureWorks2017' TO '/var/opt/mssql/data/AdventureWorks.mdf',   MOVE 'AdventureWorks2017_log' TO '/var/opt/mssql/log/AdventureWorks.ldf'

You can execute a T-SQL query on a Linux server using sqlcmd utility or SSMS if remote connections are allowed.

Executing BACKUP DATABASE and RESTORE DATABASE commands are a good way to move a database from Windows to Linux.

Problems can only arise if the source and target SQL server have different versions.

Generate T-SQL script

Another way to move a database from a Windows server to a Linux server is to create a T-SQL script. This is similar to what mysqldump or pg_dump does.

You can create a script with SSMS

The wizard will launch, in which, in addition to specifying the Output file, you will need to specify Types of data to script = Schema and Data (only Schema is selected by default)

In the created .sql file, replace the paths to .ldf and .mdf files with those that are supported in Linux:

To import a .sql file, you can use sqlcmd utility by submitting there a file with a script using the -i parameter

sqlcmd -U SA -I AdventureWorks.sql

Creating a script on a Windows server and then executing it on a Linux server is a good solution. It is convenient to work with a .sql file; before importing it, you can view it and change something if necessary.

However, as in the case with BACPAC, when exporting, it is desirable to create a snapshot to export data in a consistent state.

The main disadvantage of this method is the large file size, and a long time required to create and especially to import data.

Export\Import Data-Tier application

The database can be packed into a BACPAC file. It is a file that encapsulates a database’s schema and data. BACPAC is used to migrate a database from one server to another.

On Windows, BACPAC export can be done in two different ways:

Via SSMS

Select Export Data-Tier Application to export

The export wizard will start, where you need to specify the name and path of the .bacpac file.

If you can connect using SSMS to SQL server installed on Linux, then you can import the database via SSMS

Select Import Data-Tier Application to import

Via SqlPackage

You can create a BACPAC and import it not only via SSMS. Another way to create a BACPAC is to use SqlPackage utility.

SqlPackage is a utility that can help you migrate data between different SQL Servers. More information about it can be found here and you can download it for Linux and Windows here.

To create a BACPAC via sqlpackage, you need to run the command in the command line:

sqlpackage.exe /TargetFile:"W:\AdventureWorks.bacpac "/ Action: Export / SourceServerName:". " / SourceDatabaseName: "AdventureWorks"

Run the following command to import

sqlpackage /a:import /SourceFile:AdventureWorks.bacpac /TargetDatabaseName:"AdventureWorks" /TargetServerName:. /TargetUser:sa /TargetPassword:<your passsword>

Export\Import Data-tier Application is convenient in that you do not need to have access to the file system of the servers you are working with. Also, as a rule, BACPAC file is smaller in terms of size than .bak file, and also exported and imported faster than .sql script.

However, this method has a serious drawback. When creating a BACPAC of a live database – it may turn out to be inconsistent in terms of transactions. Therefore, before exporting BACPAC, it is advisable to either switch the database to the read-only mode or create a snapshot of the source database and export the snapshot.

SqlBak

If you need to copy the SQL Server database regularly (such as to create a test environment), Sqlbak can help you with it.

SqlBak is a service for creating backups and sending them to cloud storage. In addition, it can restore after a backup on another server.

First, you need to register and install SqlBak service on Windows and Linux server, and also configure connections to the SQL Server there. Details can be found here.

Then create a backup job on the server, from which you want to copy a database.

A new job window will pop up. From that window, select the database you want to move to Linux,

and the destination through which the backups will be moved (for example S3).

Finally, you can configure the restore task launch on another server.

After that, you can save and start the job to make sure that everything works correctly. At the end of the job log, there will be a restore task record on the Linux server.

The main advantage of SqlBak is the automation of the copy process. SqlBak allows you to schedule regular jobs, and email notifications can be configured in the event of failure.

SqlBak also supports Differential and Transaction log backups, which are much smaller and faster to create. This may come in handy for regular database cloning.

Bottom line

Thus, to migrate an SQL Server from Windows server to Linux, you just need to perform a backup on Windows and restore it on Linux. Backup can be done in different ways, sometimes it’s easier to do it through native SQL Server backup and sometimes it’s easier to create a .bacpac or .sql script.

If you need to regularly clone a server, then it is better to use third-party services such as SqlBak, which will handle file transfer and notify you in the event of failure.

Leave a Reply

Your email address will not be published. Required fields are marked *