Nisarg Upadhyay
Control flow designer

Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools

October 19, 2022 by
SQL server Quest banner

In this article, we are going to learn how to copy the SQL database created on different instances of the SQL Server. This article is the fourth article on Manage SQL Server on CentOS topic. In my previous article, Copy SQL Databases between Windows 10 and CentOS using the SQL Server import-export wizard, we learned how to copy data SQL database between windows 10 and CentOS Linux using the wizard.

In this article, we will learn how to copy databases using SQL Server data tools. We will use the Transfer database object task of SSIS to copy all objects of the Stackoverflow2010 database.

Note: if you are transferring the data of the table that is in the secondary filegroup, then you must create that filegroup in the destination database. Similarly, if the table has a partition scheme and partition function, you must create them on the destination database.

Environment setup

I have created a virtual machine on my workstation and installed CentOS Linux. I have installed SQL Server 2019 on Linux on CentOS. The IP Address of the virtual machine is 192.168.1.47. While creating the source database connection, we will use the IP Address instead of the Hostname. I have created an empty database named stackoverflow2010_Linux.

Create SSIS Package to copy data

Launch SQL Server data tools and create a new integration service project named Copy database to Linux.

New SSIS project

Drag Transfer SQL Server Objects Task from SSIS toolbox and drop it on Control flow designer. Rename it to Copy SQL database on Linux.

Drag and drop Transfer SQL Server object tasks

Now, double click on the Copy SQL database on Linux. A dialog box, Transfer SQL Server objects task editor, opens. Click on Objects to configure the Source and Destination connection.

Configure Source connection

Click on SourceConnection and select the new connection.

Transfer SQL Server object task editor

The SMO Connection Manager opens. Specify the Nisarg-PC in the Server name text box. We are using windows authentication to connect to the SQL Server database engine; therefore, select Use Windows Authentication. Click OK to save the source connection.

Connect to  Nisarg-PC

Click on SourceDatabase and select Stackoverflow2010 from the list on the Transfer database object task properties.

Select Source SQL database

Now, let us configure the destination connection.

Configure Destination Connection

Click on Destination Connection and select New Connection. On the SMO connection manager, specify the destination server. We are connecting to SQL Server on Linux, therefore enter 192.168.1.47 in Server name textbox. Select Use SQL Server Authentication. Enter sa in a Username textbox and enter the appropriate password of sa in the Password textbox.

Connect to 192.168.1.47

Back to the Transfer database object task, click on Source Database and select Stackoverflow2010_Linux from the Destination database list.

select destination SQL database

Select the database objects

We are copying all objects along with the data, so configure the Destination section as follows:

Destination Section

  1. Drop Objects First: We want to drop and create the tables, so I have set it to TRUE
  2. Copy Data: We are copying the data, so; I have set it to TRUE
  3. Copy Schema: We are copying the schema, so; I have set it to TRUE

Destination Objects

Destination Copy Options

Copy All Objects: We are copying all objects of the Stackoverflow2010 database, so I have set their value to TRUE.

Security Section

  1. Copy Database Users: We are transferring the database users, so Set it to TRUE
  2. Copy Database Roles: We are transferring the database roles, so set it to TRUE
  3. Copy SQL Server Logins: We are transferring the SQL logins, so set it TRUE
  4. Copy Object Level permission: Set it to TRUE

Export Security configuration

Table Options

  1. Copy Indexes: We are copying the indexes, so set it to TRUE
  2. Copy Triggers: We are copying the triggers, so set it to TRUE
  3. Copy Primary key: We are copying the Primary keys, so set it to TRUE
  4. Copy Foreign key: We are copying the foreign keys, so set it to TRUE

Transfer tables option

Click OK to save the configuration and close the dialog box. The SSIS package looks like the following image:

Control flow designer

Execute the package

Execute the SSIS package by clicking on the Start button from the menu bar.

Start the execution of SSIS package

The package execution begins. Once the package executes successfully, we will verify that tables and data have been copied appropriately.

Verify the schema

Now, let us verify whether all objects have been copied to the destination server or not. Open SQL Server Management studio 🡪 Connect to 192.168.1.47. Run following queries. Side by side connect to Nisarg-PC and run the following query to compare the results.

Query to get the list of tables:

Output:

Total Objects

As you can see in the above image, all objects of the SQL database have been copied to SQL Server on Linux (192.168.1.47) instance.

Query to get the Records of all tables

Run the following query on Source (Nisarg-PC) and destination server (192.168.1.47) to verify the objects and data. Side by side connect to Nisarg-PC and run the following query to compare the results.

Query

Output

Row count of all tables

As you can see, the data has been copied successfully.

Summary

This article teaches how to use the Transfer SQL Server object task to copy SQL database from SQL Server on Windows to SQL Server on Linux.

See more

ApexSQL Complete is a SQL code complete tool that includes features like code snippets, SQL auto-replacements, tab navigation, saved queries and more for SSMS and Visual Studio

 

Nisarg Upadhyay
168 Views