This guide will teach you how to seamlessly restore an MS SQL Server database (.bak file) from an AWS S3 bucket to an AWS RDS instance.
Table of Contents
Let’s Get Started
Step 1 – Upload .bak file to S3
Begin by uploading your .bak file to an S3 bucket using the AWS CLI. Open your terminal and run the following command:
aws s3 cp mydb.bak s3://your-bucket-name/Step 2 – Create an IAM Role
Navigate to AWS IAM and create a new role with the following permissions:
AWSBackupServiceRolePolicyForS3BackupAWSBackupServiceRolePolicyForS3Restore
Screenshots:
Step 3 – Configure Option Group
Go to RDS > Option groups and create a new group:
- Name: SqlServerBackupRestore
- Description: (Your Description)
- Engine: Select your DB engine
- Major Engine Version: Select your DB instance version
Screenshots:
Step 4 – Edit Option Group
After creating the group, click on its name to edit.
Add a new option:
- Select option: SQLSERVER_BACKUP_RESTORE
- Choose the IAM role created earlier
- Select ‘Immediately’ to schedule an instant change.
Step 5 – Apply Option Group to RDS
Return to RDS > Databases, select your instance, and click Modify.
Choose the Option Group you created, select ‘Apply immediately’, and click Modify DB instance.
Step 6 – Run SQL Command
Open SQL Server Management Studio and execute the following command to initiate the restore process:
exec msdb.dbo.rds_restore_database
@restore_db_name='mydb',
@s3_arn_to_restore_from='arn:aws:s3:::your-bucket-name/mydb.bak';Step 7 – Check Restore Status
To monitor the status of the restore operation, run the following SQL command:
exec msdb.dbo.rds_task_status;Wrapping Up
Congratulations! You have successfully restored an MS SQL Server database from an S3 bucket to your AWS RDS instance. This step-by-step guide ensures a systematic approach to managing your data migration efficiently. If you encounter any issues, refer to AWS documentation or seek assistance from the AWS community for prompt solutions. Happy coding!
You May Also Be Interested In
The guide from AWS regarding Importing and exporting SQL Server databases using native backup and restore.
As well as:
