Transferring data to other servers

The guide describes how to export data through the ODBC data providers.

Transferring data using the ODBC data provider

With Data Pump for SQL Server, you can migrate data not only from the file but also between different servers. For example, you can import data using native ODBC drivers or ODBC drivers provided by third-party providers, for example, Devart.

Note

Since SSMS is a 32-bit application, only 32-bit ODBC drivers should be downloaded and installed for the correct work of dbForge Data Pump.

Devart ODBC Drivers are cutting-edge connectivity solutions for 32-bit and 64-bit Windows, macOS, and Linux that can increase performance, make the deployment process much easier, and provide a fast access to databases. For more information about using Devart ODBC drivers, see How to configure Devart ODBC drivers.

Depending on the operating system you use, you can install Devart ODBC drivers using the following how-to topics:

To start using ODBC drivers in the Data Export wizard, they should be installed and configured in advance.

To configure ODBC drivers for a specific server, see the following topics:

In addition, you can import data using the ODBC provider.

To transfer data using the ODBC data provider

1. In Object Explorer, right-click a database to which you want to export data and select Data Pump > Export Data.

2. In the Data Export Wizard that opens, select the ODBC file format and click Next.

3. On the Source tab, select the connection, database, schema, and table or view, and click Next.

To search for a specific table or view in the list, start typing its name in the Search field. The list will be filtered to show only those tables that contain matching characters in their names.

Select a target table for the exported data

4. On the Options tab, set ODBC data provider options for exported data:

  • Under Data source specification, select one of the following options:

    • Use system or user data source name from the drop-down list. You can click Refresh to update the list.
    • Use a connection string. Then, click Build. In the Select Data Source dialog that opens, switch to the Machine Data Source and click New. Then, follow the steps from the above-mentioned how-to topics about ODBC driver configuration for a specific server.

  • Under Login information, enter the user name and password to connect to the server and click Next.

Optional: To verify the connection, click Test Connection. In the pop-up window informing that Data Pump was successfully connected, click OK.

Set ODBC options for the exported data

5. On the Table tab, select a target table or view (either new or existing one) for export and click Next.

6. On the Data formats tab, select columns for export and click Next. By default, all columns are selected. If you want to exclude columns from the data export, clear the corresponding check boxes.

Set data formats for the table

7. On the Exported rows tab, set a range for the exported rows and click Next.

set a range for the exported rows

8. On the Error handling tab, specify the behavior of the tool in case of errors that may arise during data export. On the tab, you can also select to write a report to a log file and specify a path to it.

Specify the error handling behavior

10. To start data export, click Export and see the progress. Data Pump will notify you whether the export was succeeded or failed.

To open the log file, click Show log file.

To launch another data import, click Export more.

To open the output export script, click Open result file.

Note

You can save the export settings as a template for future use. To save export settings, click Save Template on any wizard tab. Next time you will need only to select a template and specify a location of the source data - all the settings will be already set. For more information, see How to use data export templates.

11. To close the wizard, click Finish.