Step by step guide to perform MSSQL database backup/restore from an on-premise instance to AWS RDS.

One of the common need if you are working with any database is to perform backup and restore of databases at any given point of time.

AWS RDS supports so many databases but comes with many limitations as well.

Here, I will talk specifically about MSSQL server backup and restore functionality in AWS RDS.

You cannot restore any database from on premise instance to RDS instance directly, even through SSMS.

So, below are the steps and commands through which you can accomplish this task.

Here are steps :

1. Install AWS CLI and configure it with appropriate S3 access rights.
2. Create one S3 bucket.
3. Use below command to upload your db backup files (.bak) from local machin to S3 bucket.

aws s3 cp /source_folder_path/ s3://destination_s3_bucket_name/ –recursive –include “*.bak”

( *.bak uploads all .bak files from source folder.)

4. Go to RDS console and create new ‘Option Group’. Give group name an select appropriate db engine version.
5. Select newly created group and click on ‘Add Option’ button.
6. Select ‘SQLSERVER_BACKUP_RESTORE’ as option.
7. Click on ‘Create New Role’ and enter IAM username. Also, select s3 bucket from dropdown where you have uploaded .bak files.  (You can skip creating new role if you already have IAM user with appropriate rights.)

8. Now, select your database instance and click ‘Instance Action–> Modify’.
9. Change ‘Option Group’ to one which we have created above.
10. Save the changes and wait for RDS instance to apply the changes.
11. Once status becomes ‘available’ connect RDS instance through SSMS and run below queries to restore the .bak file

exec msdb.dbo.rds_restore_database       @restore_db_name=’put_here_desired_db_name’,       @s3_arn_to_restore_from=’arn:aws:s3:::put_here_s3_bucket_name/put_here_db_bak_file_name.bak’;

12. To see the task status of db restore, use below command.

exec msdb.dbo.rds_task_status @db_name=’put_here_desired_db_name’

13. Once done, don’t forget to change options group to default one.

Hope, this will surely save someone’s day…

References:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

https://forums.aws.amazon.com/message.jspa?messageID=736361