AWS - Restore RDS

 

Step 0: Update RDS backup file to s3

aws configure set default.s3.max_concurrent_requests 45
aws configure set default.s3.multipart_threshold 128MB
aws configure set default.s3.multipart_chunksize 64MB
aws configure set default.s3.max_bandwidth 150MB/s
aws s3 cp "C:\Backup\xxxxxx.bak" s3://xxx/xxx/

Step 1: Create RDS instance

aws rds create-db-instance \
--engine sqlserver-ee \
--license-model license-included  \
--engine-version 11.00.7493.4.v1 \
--db-instance-identifier XXXXXX \
--storage-type gp2 \
--db-instance-class db.m5.xlarge \
--max-allocated-storage 4000 \
--allocated-storage 3000 \
--master-username XXXXXX \
--master-user-password XXXXXX \
--db-subnet-group XXXXXX \
--vpc-security-group-ids XXXXXX \
--port 1433 \
--backup-retention-period 0 \
--option-group-name XXXXXX \
--publicly-accessible

Step 2: Create RDS Option Groups

aws rds create-option-group \\
          --option-group-name XXXXX \\
          --engine-name sqlserver-ee \\
          --major-engine-version 11.00.7493.4.v1 \\
          --option-group-description "restore sql server bak file"

Step 3: Add Option to Option Groups to add SQL DB Backup option from S3

aws rds add-option-to-option-group \\
          --option-group-name XXXXXX \\
          --options "OptionName=SQLSERVER_BACKUP_RESTORE,OptionSettings=
                     [{Name=IAM_ROLE_ARN,Value=arn:aws:iam::XXXXXX}]" \\
          --apply-immediately

Step 5: Connect to this RDS instance from local SQL Server DB

The proper connection details for DBeaver for me were:

• Server Host: Endpoint address
• Port: 1433
• Database: DB name
• User name: Database master username
• Password: Database master password

Step 6: Delete RDS instance

-- aws rds delete-db-instance \
--db-instance-identifier xxxxxx \
--skip-final-snapshot

SQL Server Restore backup:

1. Restore database: exec msdb.dbo.rds_restore_database @restore_db_name=‘databasename’ , @s3_arn_to_restore_from=‘arn:aws:s3:::s3bucketname/databasename.bak’
2. Check status: exec msdb.dbo.rds_task_status;