Different ways to export SQL Database using Devart Data Pump
Published: 2022-06-23 08:09:22

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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/v95hu26w69xz7pakcx5s.png)
 
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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ho8hpl0ds4evg8c0lg9m.png)
Create New Data Source on screen select SQL Server Native Client 11.0 🡪 Click Finish  

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2wq25fi8u9lnmy0cjjgw.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zqfvcw9fu1ioqqmxw2lm.png)
 
On the next screen, specify the authentication method used to connect to the SQL Server. We are using Windows Authentication.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j1bodwvpukbeacidmzzo.png)
 
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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4jmn62j1fscbxk5l9154.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qm0vkozupfqcx9i367mb.png)

A popup will be displayed. In the popup, you can see all the configuration parameters and the value set by us. 
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5i5qwh70jbxnw9dhxhwk.png)

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.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/debgtcly321m8keqv3w6.png)

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](https://www.devart.com/dbforge/sql/data-pump/). 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**.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bb5t8iylj9p1blcefpdl.png)
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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5fpinbvs1tuuml8otdli.png)
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**.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vrwhvf4t0xa1t5janpha.png)
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**.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4liefnw7iwsbxegs1yja.png)

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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w7ba29albh71p4aw4t2x.png)
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**.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r5n5qm9504kfroce8kxb.png)
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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ifikegaipm9z5rfmhm6i.png)
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.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/r1wquuvbo2g38bx9sgxg.png)
The data export process begins.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8zztpa8ohgvs15gcoti7.png)
The data has been exported successfully.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ioqf1qpjts06lr70h3dx.png)
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**
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4qk3p0qfpz235gtdpmwq.png)
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.