• Relational Database Service

rds
  1. Help Center
  2. Relational Database Service
  3. User Guide
  4. Working with RDS for Microsoft SQL Server
  5. Migrating SQL Server Data Using SQL Server Management Studio
  6. Exporting Data

Exporting Data

Before migrating an existing Microsoft SQL Server database to RDS, you need to export the Microsoft SQL Server database.

  • The export tool must match the DB engine version.
  • Database migration is performed offline. Before the migration, you must stop any applications using the source database.

Procedure

  1. Log in to the ECS or device that can access RDS prepared in 1 in section Preparing for Data Migration.
  2. Use SQL Server Management Studio to generate database object scripts such as tables and views.

    1. Use SQL Server Management Studio to connect to the Microsoft SQL Server database.
    2. On Object Explorer, expand Databases, right-click the database to be exported, and choose Tasks > Generate Scripts. The Generate and Publish Scripts window is displayed.
    3. Choose Choose Objects in the navigation pane on the left, select database objects to be exported, and click Next.
    4. Choose Set Scripting Options in the navigation pane on the left, click Save script to a specific location, and select a path for storing exported files from the File name drop-down list, and click Next.
      NOTE:
      • If you select Single file, all objects will be stored in the same file.
      • If you select Single file per object, each object will be stored in its own file.
    5. Click Next.
    6. Click Finish.
    7. Use SQL Server Management Studio to open the exported SQL file or SQL files.
    8. Change USE [DATABASE] in the first line to USE [RDS database name] and save the change.
    NOTE:

    For details on generating scripts, see Generate and Publish Scripts Wizard.

  3. Use bcp to export data from the source database to a .txt file.

    bcp dbname.schema_name.table_name out C:\test\table_name.txt -n -S localhost -U username -b 2000

    • -n indicates that the native (database) data types are used for performing bulk-copy operations.
    • -S indicates the address to be used by the bcp tool to connect to the Microsoft SQL Server DB instance.
    • -U indicates the database username.
    • -b indicates the lines of data imported in a batch.

    Enter the database password as prompted.

    Example:

    C:\test>bcp test.dbo.t1 out c:\test\t1.txt -n -S localhost -U rdsuser -b 2000

    Enter the database password as prompted.

    After this command is executed, a t1.txt file will be generated as follows:

    C:\test>$ dir t1.txt
    2017/03/27  11:51         22 t1.txt

    Repeat the preceding steps to export data from the other tables in the database.