How to Perform Native Backup with AWS RDS SQL

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.

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.

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:

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

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

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

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

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

Choose “Apply immediately” option:

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

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:

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‘;

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!