Amazon RDS – Creating a native (.bak) Backup of a SQL Server Database in RDS

Category : Articles Published : November 20, 2016 User Rating : 4.5 Stars      Views : 4 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

Introduction

The original release of RDS had no ability to create a native database backup or to restore from a native backup (i.e. a .bak file). This was a major restriction as it meant that an individual database could only be moved in or out of RDS by scripting it up using tools such as the SQL Server Import and Export Wizard. This all changed in July 2016 when AWS announced that they had implemented support for native backups in this article : https://aws.amazon.com/about-aws/whats-new/2016/07/amazon-rds-sql-server-supports-native-backups/.

If you have your own on-premise database that you want to move to RDS you can simply back it up, upload it, and restore it onto an RDS SQL Server. Equally if you want to copy an RDS database to your own local SQL Server instance (perhaps a development machine) then you can do that too using backup and restore. You can also copy a single database from one RDS server to another in the same way.

In my view this is a huge step forward, and gets rid of a major hurdle, especially if you are moving from on-premise SQL Server to RDS hosted SQL Server. There are a few limitations which are outlined in this AWS article : http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html ).

I'll now summarise the steps involved in backing up an RDS database and restoring it to a local SQL instance.

Creating a Backup of an RDS Database and Restoring it to an on-premise Database

The key steps involved in this are :
  • Step 1   Create an S3 storage bucket in the same region, if you don’t have one already.
  • Step 2   Assign the RDS SQL Server Instance to an option group containing the SQLSERVER_BACKUP_RESTORE option.
  • Step 3   Run a special stored procedure to back up the RDS database to the S3 storage bucket.
  • Step 4   Download the backup file from S3 to the local SQL Server, and then restore the backup to the local server using Management Studio.
I’ll explain the steps above in a bit more detail with screenshots below. Note that I've assumed in this article that your instance has been assigned to the default option group, which occurs automatically when you create a new instance.

Step 1 : Create an S3 storage bucket

If you don’t already have an S3 bucket (which needs to be in the same region as the RDS instance) you can either create one using the instructions on the AWS site http://docs.aws.amazon.com/AmazonS3/latest/gsg/CreatingABucket.html , or there is an option to do this while setting up the option group, which is described in the next step.

Step 2 : Creating the Option Group and Assigning to the RDS SQL Server Instance

Once logged into the AWS console and on the RDS page, click on the 'Option Groups' menu on the left hand menu, then click on the 'Create Group' button as shown below :
Create new option group Enter details in the Create Option Group form as below. Note that the option group is specific to the database engine edition and version, so you may need to alter the options accordingly if you are using different engine editions and versions :
Create new option group Once the option group is created you need to add the option to it, by selecting the Option Group we've just created and clicking on the 'Add Option' button as shown below :
Create new option group From the 'Add Option' window select the SQLSERVER_BACKUP_RESTORE from the drop down, and make sure ‘Apply Immediately’ is set to ‘Yes’. You’ll also need to create an IAM role to link to your S3 bucket, by clicking the ‘Create a New Role’ hyperlink. Once you’ve done this, if you don’t already have an S3 bucket then you can create one by clicking the ‘Create a New S3 Bucket’ hyperlink :
Create new option group Click on the 'Add Option' button to create the option in the option group. Once that’s done you’ll be able to see the new option group in the AWS console, under Option Groups :
Create new option group The final step is to change the option group for the RDS instance from the default one to the one we’ve just created. To do this go to the ‘Instances’ menu item, select the relevant RDS instance, then click the ‘Modify’ link under ‘Instance Actions’ drop down. Scroll down until you see the ‘Database Options’ area. Change the Option Group to ‘sql-rds-native-backup’ (or whatever you called it) and make sure you click the ‘Apply Immediately’ checkbox, as below :
Create new option group Click the 'Continue' button, which then completes the set up required in the AWS console. A native backup file can now be created as and when required by running SQL statements, which I'll describe in the next step.

Step 3 : Running a Stored Procedure to Create the Backup

To start a backup of a database you’ll need to run an AWS stored procedure, specifying the database, S3 bucket name, and backup file/folder name, as follows :
-- Backup databases
exec msdb.dbo.rds_backup_database
       @source_db_name='database_to_be_backed_up',
       @s3_arn_to_backup_to='arn:aws:s3:::S3_bucket_name/folder/backup_name.bak',
       @overwrite_S3_backup_file=1;

This returns immediately, but you can check on the progress of the backup using the following stored proc :
-- Check status of backup
exec msdb.dbo.rds_task_status @db_name='database_to_be_backed_up'

There are other options for these stored procs (for instance to encrypt the backup), which are documented on the AWS site.

Step 4 : Download the backup file from S3 to the local SQL Server

Once the backup is complete you can navigate to the S3 page on the AWS console. From there you should be able to navigate to the S3 bucket and click on the relevant backup to download to your local server. Alternatively you can use a third party tool such as CloudBerry Explorer for S3 (which I would recommend). The backup file can be restored to your local server using SQL Server Management Studio.

Link back to this article : https://www.sqlmatters.com/Articles/Amazon RDS Creating a native bak Backup of a SQL Server Database in RDS.aspx

Keywords

RDS, AWS, Amazon, SQL 2012, 2008R2, 2014, Backup TSQL


Comments
Post by shaeredal on Fri 02 Jun 2017 11:17. Report Inappropriate Post
Website : http://shaeredal.github.io/
Thank you. This article is very helpful!
Post by naga ramesh on Wed 12 Jul 2017 19:36. Report Inappropriate Post

superb boss, for me v v v help full ...
Post by Bariq on Wed 22 Aug 2018 15:28. Report Inappropriate Post

Hi,
Thanks for the nice article
Iam wondering if there is a way to automate the backup process from sql server to s3, if there is any way by lambda or any other service to generate .bak file to moving it to s3.

//Regards
Post by steven on Sun 09 Sep 2018 03:24. Report Inappropriate Post

backup job seems not to be exeuted immediately or in a short time. I have been waiting for at least 10 minutes, and still can't find 'Created' in the status returning outputs of msdb.dbo.rds_task_status. I tried to cancel the task , aws returned me "sg 50000, Level 16, State 1, Server EC2AMAZ-99VV0NB, Procedure msdb.dbo.rds_cancel_task, Line 17 annot find a CREATED or an IN_PROGRESS task: 4 to cancel.".....................Ha..ha.....Anyway, I think AWS doesn't encourage you to perform a native backup to S3, because they guess you want to migrate mssql rds db to on-premise or other cloud vendor. However, they accept restore task promptly. Ha....................
Post by Benjamin Pinto on Tue 11 Feb 2020 03:26. Report Inappropriate Post

Thank you!
Post by Ward on Tue 14 Jun 2022 21:16. Report Inappropriate Post

This article is good but it really misses the prerequisite of creating a IAM policy and role for the RDS to access the S3 bucket. Especially since AWS no longer has the console as shown in your screen shots. But I must admit I can never get IAM roles and policies to work properly.
I have run the query and I get result saying the file is created but the backup file never shows up in the S3 bucket.

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered