Migrate MsSQL Database to Amazon RDS

Migrate MsSQL Database to Amazon RDS

Migrate MsSQL Database

Amazon Relational Database Service (Amazon RDS) supports native backup and restore for Microsoft SQL Server databases using full backup files (.bak files)
But in this article we will migrate database from mssql server to Amazon RDS with out using any .bak files. The approch is very simple. First we generate a database structure schema from local MsSQL database server and execute this schema on Amazon RDS. Then we export data from local MsSQL database to Amazon RDS.

Export Schema from current MsSQL Server

1) Open SQL Server Management Studio.
2) Connect to the local database server.
3) Select database which you want to export to RDS, right click on it and go to Task >> Generate Script

Migrate MsSQL Database 1

4) Select the database objects which you want to export.

Migrate MsSQL Database 2
There are two options here. Based on your requirement select one. For my requirement I selected second option.
By database have only Tables object, othere objects can be Views, Stored Procedures, User-defined Functions, Users etc…
Don’t select the Users. Every new database will already have Users object.

5) Save generated script which we will use on RDS database.

Migrate MsSQL Database 3
Based on your requirement choose the option. For my requirement I opted to save it to the file so that I can use it later.

6) Click Next and Next.
7) Wait until the script is created.

Execute database schema on amazon RDS

  1. Open SQL Server Management Studio.
  2. Connect to the Amazon RDS server.
  3. Create a new empty database by right clicking on Databases.
  4. Copy generated script from management studio or from the saved file and run in command prompt.
  5. Now if you check the database, it contains all the tables.

Migrate MsSQL Database to Amazon RDS (Data)

Now Migrate MsSQL Database data from the source database (local MsSQL database server) to the new database (Amazon RDS database). SQL Server Import and Export Wizard is used to accomplish this. You open this by right clicking on the source database and click Tasks > Export…

  1. First enter the connection details for the source database.
  2. Then enter the connection details for the target database.
  3. Select all the tables whose data has to be copied to destination database.
  4. Click on Edit Mappings…and select Enable Identity Insert check box.
  5. Click Next. Choose Run immediately,
  6. Click Next, Next and Finish.