How to Restore Backup from S3 to RDS in SQL Server

What is the difference between Amazon S3 and Amazon RDS

Amazon S3 (Simple Storage Service) and Amazon RDS (Relational Database Service) are both managed services offered by Amazon Web Services (AWS), but they serve different purposes and have different features.

Amazon S3 is a cloud-based object storage service that allows you to store and retrieve data, such as files, images, videos, and documents. It provides high durability, scalability, and availability, making it an ideal solution for storing and managing large volumes of unstructured data. Amazon S3 is highly flexible and can be used for a wide range of use cases, such as backup and restore, content delivery, and data archiving.

On the other hand, Amazon RDS is a managed database service that allows you to deploy, operate, and scale relational databases in the cloud. It supports several popular database engines, such as MySQL, PostgreSQL, MariaDB, Oracle Database, and Microsoft SQL Server. Amazon RDS provides automated backups, software patching, monitoring, and scaling features, making it easier to manage databases in the cloud. Amazon RDS is well-suited for applications that require structured data and transactions, such as e-commerce applications, financial systems, and content management systems.

How to restore backup from S3 to RDS SQL server

Restoring a backup from Amazon S3 to Amazon RDS in SQL Server is a straightforward process that enables you to recover critical data, migrate databases, and set up new SQL Server instances effortlessly. Here are the steps about how to restore database in AWS RDS SQL server:

1. Make an on-premises database backup. Right-click the database in SQL Server Management Studio, select Tasks, and then choose Backup. It's crucial that you specify what your backup's extension is. Click OK.

2. Access the AWS console. Go to RDS and select Option Groups from the left-hand menu.

3. In option groups, click on Create Group.

4. Give a Name, Description, Engine and Engine version. Your AWS SQL server instance's edition should be used as the engine in this case. Additionally, the version must match the version that was supplied when the instance was created.

Note: Create a newer database instance in AWS rather than your on-premises database because restoring to earlier versions from newer versions may result in issues.

5. Your SQL Server Backup file must be in an S3 bucket in order to continue. Create a new bucket and upload a .bak file to restore SQL database.

6. Return to RDS and choose the freshly established option group. Select the option group after it has been created, then click Add option.

7. Next, select the option “SQLSERVER_BACKUP_RESTORE” from the drop-down list. It should be already selected. Select Yes to Create a new role, then give a role name.

Choose the S3 bucket you created in the list below, then click Yes to Apply immediately. Next, select Add Option.

8. Navigate to Databases, and select the database you want to have restored from the S3 bucket. From the upper right corner, select Modify.

9. Access to Database options. Then under option group, select the newly created option group, and click Continue. Choose Apply immediately on to avoid having to wait until the following maintenance window. Next, select Modify DB Instance.

10. Open up SQL Server Management Studio, connect to your AWS RDS database instance and open a new query window.

11. Then execute following command.

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

You can now verify the status of the restore task by executing the following command:

EXEC msdb.dbo.rds_task_status;

Conclusion

In summary, it entails various steps and tools to restore backup from S3 to RDS SQL Server, including configuring RDS instance settings and using SSMS to perform AWS RDS restore from S3. Securing data is also crucial when restoring backup data from S3 to RDS SQL Server. Some best practices for securing data may complex while AOMEI Cyber Backup provides various security features to help you protect your data.