Help Center > > Best Practices> Microsoft SQL Server> Using the Import and Export Function to Migrate Data from a Local Database to an RDS Microsoft SQL Server DB Instance

Using the Import and Export Function to Migrate Data from a Local Database to an RDS Microsoft SQL Server DB Instance

Updated at: Oct 23, 2019 GMT+08:00

Scenarios

  • You have created a local Microsoft SQL Server database.
  • The local database version cannot be later than the version of the destination RDS Microsoft SQL Server DB instance.
  • You want to migrate only tables instead of the whole database.

Procedure

  1. Log in to the RDS console. On the Instance Management page, click the target DB instance name.
  2. On the EIPs page, click Bind EIP. In the displayed dialog box, select an EIP and click OK.
  3. Install the SSMS client locally and use the EIP to connect to the RDS DB instance.

    Click here to download the SSMS client.

  4. Use the script generation tool provided by Microsoft SQL Server to generate a database structure script.

    1. Right-click the database whose schema script needs to be generated and choose Tasks > Generate Scripts.
    2. On the Choose Objects page, choose database objects to script, as shown in Figure 1. Then, click Next.
      Figure 1 Choosing objects
    3. On the Set Scripting Options page, specify a directory for saving the script.

      You are advised to save the script locally and generate an SQL script for execution.

      Figure 2 Specifying a directory for saving the script
    4. Click Advanced. In the displayed Advanced Scripting Options dialog box, specify scripting options for triggers, indexes, unique keys, the primary key, and server version. Then, click OK.
      Figure 3 Specifying advanced scripting options

      Generate Script for Dependent Objects indicates the script data type option.

    5. Click Next to generate the script.

  5. Use the SSMS client to connect to the RDS DB instance and open the generated SQL script.

    You need to create an empty database, and then use the script to create structures in the database.

  6. Use the import and export function provided by Microsoft SQL Server to migrate data.

    1. Right-click the database whose data needs to be exported and choose Tasks > Export Data.
    2. On the Choose a Data Source page, select a data source and click Next.

      Data source: Select SQL Server Native Client or specify the option based on your data source type.

      Server name: Enter the IP address and port number of the source DB instance. If the source DB instance is local, enter the DB instance name or localhost.

      Authentication: Select Use SQL Server Authentication. Then, set User name to rdsuser, and Password to the password of rdsuser.

    3. On the Choose a Destination page, select a destination database and click Next. On the displayed Specify Table Copy or Query page, select Copy data from one or more tables or views. Then, click Next.

      The destination database indicates the database to which your data is imported.

    4. On the Select Source Tables and Views page, select the tables and views that you want to export. Then, click Edit Mappings. In the displayed dialog box, select Enable identity insert and edit mappings based on your requirements.
    5. Click Finish to export and import data.

      You can view the progress. About 4,000 rows can be processed per second.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel