The Devart Data Pump is a SQL Server Management Studio add-in that helps us migrate data between SQL Server databases to various data formats. The Devart Data Pump supports import and export of Microsoft Excel, Microsoft Access, CSV, Text, JSON, PDF, HTML, ODBC, etc. The Devart Data Pump is highly customizable, so the data export process becomes much easier.

In this article, we will learn the step-by-step process of exporting data from the Azure SQL database to an on-premises SQL database server with the help of efficient SQL tools. We are using the ODBC data source to connect to the on-prem server.

Environment Setup

I have configured Azure SQL Server and configured a sample database named AdventureWorksLT database. The details of the Azure SQL Server details are as follows:

We will export the records of the customer table, which is created in the AdventureWorksLT database.
I have installed SQL Server 2019 on my workstation and created a database named DemoDatabase. I have scripted out the table definition of the customer table from the AdventureWorksLT database and re-created it on DemoDatabase.
The script to create the table is as follows:

CREATE TABLE  [Customer]
(
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [NameStyle] BIT NOT NULL,
    [Title] [nvarchar](8) NULL,
    [FirstName] VARCHAR(100) NOT NULL,
    [MiddleName] VARCHAR(100) NULL,
    [LastName] VARCHAR(100) NOT NULL,
    [Suffix] [nvarchar](10) NULL,
    [CompanyName] [nvarchar](128) NULL,
    [SalesPerson] [nvarchar](256) NULL,
    [EmailAddress] [nvarchar](50) NULL,
    [Phone] NVARCHAR(25) NULL,
    [PasswordHash] [varchar](128) NOT NULL,
    [PasswordSalt] [varchar](10) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL) ON [PRIMARY]
GO

Now, to copy data to DemoDatabase, we must first create an ODBC data source.

Configure ODBC Driver for SQL Server.

First, let us configure an ODBC data source. Open ODBC Data Sources 🡪 Click Add on User DSN tab.

Create New Data Source on screen select SQL Server Native Client 11.0 🡪 Click Finish

The Create ODBC driver for SQL Server configuration wizard opens. On the first screen, specify the desired ODBC driver name. Specify the server hostname on which SQL Server is installed in the Server Name box and click Next.

On the next screen, specify the authentication method used to connect to the SQL Server. We are using Windows Authentication.

On the next screen, we can configure the following parameters.
Set the default database. In our demo, I set the default database to DemoDatabase.

• Specify the SPN for mirroring server
• Enable ANSI quoted identifier, ANSI nulls, paddings, and warnings.
• Application Intent. It can be READWRITE or READONLY.
• Enable multi-subnet failover.

In our demo, I have not made any changes. Click Next.

On the wizard's next screen, you can specify the following options.

• Change the SQL Server system message language.
• Enable strong encryption

• Enable the regional settings for currency, number, date, and time.

• Specify the location of the log of long-running queries and query timeout

• Specify the ODBC driver statistics log file.
  In our demo, I have not made any changes. Click Finish.

A popup will be displayed. In the popup, you can see all the configuration parameters and the value set by us.

Click OK to create the data source.

Once the data source has been created successfully, you can view it in the User DSN tab of the ODBC Data Source Administrator.

Now, let us configure the Devart Data Pump to export data from Azure SQL Database to On-prem SQL Server.

Configure Devart Data Pump

The Devart Data Pump SQL tool is an efficient SQL server import and export wizard. It can be launched from SQL Server Management Studio. I am exporting data from AdventureWorksLT (Azure SQL Database) to DemoDatabase (on-prem SQL Server database). Open SQL Server Management Studio 🡪 Connect to Azure SQL Server instance 🡪 Expand Databases 🡪 Right-click the AdventureWorksLT database 🡪 Hover on Data Pump 🡪 Select Export Data.

The Data Export wizard starts. On the first screen, you can see the various export formats supported by the Devart Data Pump. I am exporting the data in ODBC format, so I have selected ODBC.

We need to specify the source database and schema on the Source screen. We are exporting data from the customer table created in the AdventureWorksLT database. Select AdventureWorksLT from the Databases drop-down box and select dbo from the Schema drop-down box. The list of tables created in the dbo schema will be loaded in the grid view. We are exporting data from the customer table; therefore, select it from the grid and click Next.

We must set the specification of the ODBC data providers from the Options screen. In the data source specification section, we must specify the ODBC connection string or select the pre-configured ODBC system or user data source. We have already configured an ODBC data source named AdventureWorksDB, so select it from the Use system to the user data source name drop-down box. Click Next.

You can select the table where you want to export the data from the table. In our demo, we have created a customer table in DemoDatabase; therefore, choose demodatabase.dbo.customer from the list.

Suppose you want to export data from specific columns of a table. To do that, you can select those columns from the Data Format screen. This option is useful when exporting data from specific columns to Excel or CSV files. In this demo, we are exporting all columns. Click Next.

We can export all rows of tables or specific rows of tables from the Exported rows screen. In this demo, I am exporting all records from the customer table; therefore, I have selected the Export all rows option.

We can configure the error handling process and behavior on the Error Handling screen. In this demo, I am not changing any parameters. Click Export to begin the data export between the Azure SQL database to the In-Prem SQL Server database.

The data export process begins.

The data has been exported successfully.

Run the SELECT statement to verify that data has been copied successfully.
SQL Query

USE demodatabase
GO
SELECT c.CustomerID,c.Title,c.FirstName+' '+c.MiddleName +' '+c.LastName,c.CompanyName,c.SalesPerson,
c.EmailAddress,c.Phone,c.ModifiedDate FROM Customer c

Query output

As you can see, all records have been transferred.

Summary

In this article, we have explored several ways of using Devart Data Pump for exporting SQL databases. In my next article, we will learn how to transfer data from the Azure SQL database to Microsoft Excel files and other data formats using the Devart Data Pump.