How to Perform Native Backup with AWS RDS SQL

Image Title

posted by Phi-lac Nguyen
on Oct 16, 2017

The purpose of this article is to explain how to perform a native backup with AWS RDS SQL.

Requirements:
• An Amazon S3 Bucket to store the backup file
• An AWS Identity and Access Management (IAM) role to access the bucket
• The SQLSERVER_BACKUP_RESTORE option

1.Create the S3 Bucket

The procedure to create the S3 bucket is quite simple. Once you are on the Amazon S3 service, click on the “Create bucket” button.

AWS – Native backup of an RDS SQL Server DB instance

Define the name of the bucket, properties, and permission. To store the SQL backup into this bucket you have to make sure that the user has the required permissions.

AWS – Native backup of an RDS SQL Server DB instance

2. Option group

From Amazon RDS, we are going to create an option group that will allow us to perform the native backup from SQL Studio Management.

Define the name of the option group and choose the engine + version of your SQL instance:

AWS – Native backup of an RDS SQL Server DB instance 3

Select your option group and click on the “add option” button:

AWS – Native backup of an RDS SQL Server DB instance 4

Make sure the SQLSERVER_BACKUP_RESTORE is selected and then create a new IAM role.

AWS – Native backup of an RDS SQL Server DB instance 5

Select the S3 bucket created and then select “Apply immediately – YES”.

AWS – Native backup of an RDS SQL Server DB instance 6

You can see that the option was added to the option group:

AWS – Native backup of an RDS SQL Server DB instance

Then you have to modify your instance and assign the new option group:

AWS – Native backup of an RDS SQL Server DB instance AWS – Native backup of an RDS SQL Server DB instance

Choose “Apply immediately” option:

AWS – Native backup of an RDS SQL Server DB instance

On the details of your instance, you will see the option group created.

AWS – Native backup of an RDS SQL Server DB instance

3.Backup and Restore

Here the SQL query to perform the backup:

exec msdb.dbo.rds_backup_database

@source_db_name=’database_name‘,

@s3_arn_to_backup_to=’arn:aws:s3:::bucket_name/file_name_and_extension‘,

@overwrite_S3_backup_file=1;

example:

AWS – Native backup of an RDS SQL Server DB instance

Here the SQL query to restore:

exec msdb.dbo.rds_restore_database

@restore_db_name=’database_name‘,

@s3_arn_to_restore_from=’arn:aws:s3:::bucket_name/file_name_and_extension‘;

backup with AWS RDS

And that’s all the steps you need to know to perform a native backup with AWS RDS SQL! Got questions? Feel free to message us via email or comment box below!

***

NIFTIT is a development and design agency in New York, Vietnam & Hong Kong. We are dedicated to empowering businesses of all sizes and non-profit organizations through nifty solutions. Our team develops customized solutions according to the best business practices in the industry. In our weekly blog, we provide the latest news and useful technical blogs about SharePoint, Office 365 and Power BI. Don’t hesitate to subscribe to our newsletter!