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

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 management console.
2. Click in the upper left corner and select a region and a project.
3. Click in the upper left corner of the page and choose Databases > Relational Database Service.
4. Log in to the RDS console. On the Instances page, click the target DB instance name.
5. In the navigation pane on the left, choose Connectivity & Security.
6. In the Connection Information area, click Bind next to the EIP field.
7. In the displayed dialog box, select an EIP and click Yes.
8. Install the SSMS client locally and use the EIP to connect to the RDS DB instance.
   

   Click here to download the SSMS client.

9. 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.

10. 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.

11. Use the import and export function provided by Microsoft SQL Server to migrate data.
    1. Right-click the database where data is to be imported and choose Tasks > Import Data.
    2. Click Next.
    3. On the Choose a Data Source page, select a data source and click Next.
    4. On the Choose a Destination page, select a destination database and click Next.
       • Destination: Select SQL Server Native Client (depending on your destination database type).
       • Server name: Enter the IP address and port number of the destination DB instance.
       • Authentication: Select Use SQL Server Authentication. Then, set User name to rdsuser, and Password to the password of rdsuser.
       • Database: Select the destination database where data is to be imported.
    5. Select Copy data from one or more tables or views and click Next.
    6. On the Select Source Tables and Views page, select the tables and views that you want to copy. Then, click Edit Mappings. In the displayed dialog box, select Enable identity insert and edit mappings based on your requirements.
    7. Click Next.
    8. Select Run immediately and click Next.
    9. Click Finish to import data. You can view the progress. About 4,000 rows can be processed per second.