This article gives you an overview of creating backups of AWS RDS SQL Server database instances using AWS Backup service.
Introduction
Database backups and restoring is a very standard process and an integral part of any database administration job. In real-life scenarios, there can be tens to hundreds of database instances, in various environments like development, testing, and production. Although one can perform individual database backups, often it’s not an efficient process and cannot be easily scaled. Administrators often group databases and perform backups in batch modes instead of performing individual backups as part of their routine backup process. AWS RDS service supports SQL Server as one of the databases. Organizations routinely create a large number of AWS RDS SQL Server database instances as part of their production workloads, and with it comes the need to back up these database instances. AWS RDS for SQL Server provides options to create backups using AWS RDS itself. There is a specialized service in AWS to perform volume backups on not only AWS RDS databases but many other resources as well. This service is known as AWS Backup which addresses the exact scenario mentioned above. In this article, we will be exploring the process of creating on-demand backups on AWS SQL Server database instances using AWS Backup service.
AWS RDS SQL Server Instance
It’s assumed that a functional instance of Amazon RDS SQL Server is already in place. Beginners on AWS RDS for SQL Server, can read this article, Getting started with AWS RDS SQL Server, to create a new instance. Once you have a working instance, it would look as shown below. For this exercise, any SQL Server instance using any edition of SQL Server will work. It is assumed that you have the required privileges to administer the SQL Server database instance.
AWS Backup
Now let’s say that we intend to backup the AWS RDS SQL Server instance. Navigate to AWS Backup by opening the AWS Console home page, search for the word backup, and open the service titled AWS Backup. Once you navigate to the home page of AWS Backup, you can see the dashboard as shown below. This dashboard offers different functionalities like creating a new on-demand backup, creating scheduled backup plans, restoring a backup and other backup related functionalities.
We intend to create an on-demand backup of AWS RDS SQL Server instance, so click on create an on-demand backup. After clicking, we would be navigated to a page where we can start providing the details.
First, we need to select the type of resource we intend to backup. AWS Backup supports different types of resources like RDS, EC2, DynamoDB and few others. For our exercise, we need to select RDS as the resource type as shown below.
Now we need to select the instance name i.e. the database name as shown below. Generally, AWS Backup discovers your instance and lists it. In case you have many instances in your AWS RDS environment, you can also search for your instance by typing the instance name.
We intend to create the backup immediately, so we would select the option Create backup now. If you want to create a window when the backup should start, you can select the Customize backup window. We can ignore the transition storage option for now as it would not apply. The next setting is regarding when the backup should be considered as expired so that it can be removed. You can select any option as per the requirements. We would be deleting the backup immediately after creating a backup successfully. So you can select any setting or keep the default value.
All the backups that are created by AWS Backup are stored in a logical container called a backup vault. You can logically group backups in the vault and perform batch operations on objects stored in a vault. For now, we can use the vault that is already created by default. Alternatively, you can also create a new backup vault by clicking on the Create new backup vault button and providing the required details.
The next setting is regarding the IAM role which will be used by AWS Backup to communicate with AWS RDS SQL Server instance and perform the administrative action to create a backup. Use the default role and click on the Create Backup button.
As we used the default IAM role, which is the AWSBackupDefaultRole, it may not have the required privileges to perform administrative actions like Backup on RDS. The solution is either to create a new role that has the required privileges, or you can modify the existing IAM role and add the AWS RDS required privileges as shown below. Here we added AmazonRDSFullAccess privileges to the IAM Role AWSBackupDefaultRole as a quick fix, but ideally, you may want to add a more restrictive policy to this role.
Navigate back to the AWS Backup page, and using the default IAM role, click on the Create Backup button and you would be able to create the backup job successfully as shown below. The backup job would be in Creating status and the job has just started the backup process.
Click on the backup job name, and you would find the details as shown below. Recovery point ARN (Amazon Resource Name) is the link to the actual backup when it would be created. If for some reason one needs to stop the backup, one can click on the Stop button to terminate the backup job. The job can take some time to complete the backup on the AWS RDS SQL Server instance.
Once the backup job is complete, you would be able to see the status of the job as Completed. As shown below, if you navigate to the Backup vaults from the left pane and open the default vault as shown below, you would find the backup job listed with its status as Completed.
Interestingly, if you navigate to AWS RDS and open the AWS RDS SQL Server database instance, open the backup tab from the dashboard, you would be able to find this backup listed in the Snapshot section as well. You can view restore this snapshot but you won’t find an option to delete the snapshot from this console, as this is controlled by AWS Backup service.
Navigate back up the recovery point in the completed AWS Backup Job as shown below. From here, you can copy this backup, restore it as well as delete the backup. Now that we have learned how to create a backup of AWS RDS SQL Server instance, we can remove this backup. Click on the Delete button, and provide confirmation to delete this backup.
Once the backup is deleted, you would receive the confirmation message and the backup section would be empty as shown below. As the backup is removed, we would not incur any charges for the backup.
If you cross-check again in AWS RDS by navigating to the Backup section, you would find that the backup entry which we saw earlier would not appear now in the backup section as we have deleted it.
In this way, we can administer backups of multiple AWS RDS SQL Server instances and other resources using AWS Backup service.
Conclusion
In this article, we learned how to use the AWS Backup service to create on-demand backups for AWS RDS SQL Server instances. We also learned how to configure the IAM roles required by AWS Backup service to create backups of AWS RDS database instances. Consider creating backups and restoring those backups in your database instances, as well as scheduling backup jobs using the steps explained above.
Table of contents
He has worked internationally with Fortune 500 clients in various sectors and is a passionate author.
View all posts by Rahul Mehta
- Finding Duplicates in SQL - February 7, 2024
- MySQL substring uses with examples - October 14, 2023
- MySQL group_concat() function overview - March 28, 2023