How to restore AWS RDS SQL Server database from S3 bucket using SSMS.

We all have been there where you get to know the pain of restoring a database from backups makes you hate all databases in the world, which is most the time not as easy as backing it up. To make things worse when I wanted to find a way to restore RDS database from my on premise SQL Server, I couldn’t find a flawless way to do it. If you are like me, who wants to do it with the best way possible, this is for you.

First you need to create a backup of your on premise database. Open up SQL Server Management Studio, right click on the database, go to Tasks then select Backup.

It is important that you set the extension of your backup as .bak and click OK.

My backup file is ‘model.bak’

Now login to AWS console navigate to RDS, go to option groups from the left pane.

Databases is where all your databases are listed. What we want is Option groups.

In option groups, click on Create Group.

Give a name, description, engine and engine version. Here the engine should be the edition of your AWS SQL server instance. And the version should be same as the version you specified when you created the instance.

sqlserver-ex means “express edition”, sqlserver-ee means “enterprise edition” etc.

Note: Restoring to older versions from newer versions may cause problems, better to create a newer database instance in AWS than your on premise database.

To proceed from here, your SQL Server Backup file should be in an S3 bucket. Create a new bucket and upload previously created “.bak” file. This tutorial gives step by step instructions to how to upload a file to S3 bucket.

Now come back to RDS and select the newly created option group. Once the option group is created select it, and click on Add option.

In the next screen Select the option “SQLSERVER_BACKUP_RESTORE” from the drop down list. It should be already selected.

Select “Yes” to “Create a new role”., and give a role name.

Down below, select the S3 bucket that you have created, Select “Yes” to “Apply immediately”. Then click “Add Option”.

Now go the Databases, select the database that you want to get restored from the S3 bucket. Click on “Modify” from the upper right corner.

Scroll down until you find Database options. Then under option group, select the newly created option group, and click “Continue”.

From next screen select “Apply immediately”, so you do not have to wait until the next maintenance window. Then click on “Modify DB Instance”.

Now open up SQL Server Management Studio, Connect to your AWS RDS database instance and open a new query window.

Then execute following command.

exec msdb.dbo.rds_restore_database @restore_db_name=’<batabase_name_after_restore>’, @s3_arn_to_restore_from=’arn:aws:s3:::<bucket_name>/<backup_file_name>.bak’;

e.g.

exec msdb.dbo.rds_restore_database @restore_db_name=’TestDatabase’, @s3_arn_to_restore_from=’arn:aws:s3:::myBucket/DBBackup.bak’;

When you execute this command, you will see following result.

Now the restore task has begun, You can check it’s status by running the following command.

EXEC msdb.dbo.rds_task_status;

This command will give you following result. See that the LifeCycle is IN_PROGRESS

That’s it. Hope this was helpful. Don’t hesitate to ask any questions that you have.