Restore SQL Server Backup on AWS RDS

Apr 8, 2023

When restoring mymssqldb.bak on AWS RDS SQL Server, the file should be uploaded to S3 first, then RDS will restore from the file in S3.

AWS Free tier has limited features. The steps below may have additional cost.

S3

Create a bucket on S3 — the name cannot be changed after creation. I named it mymssqldbbak. AWS Region should match the region in AWS RDS SQL Server.

Upload mymssqldb.bak to the bucket.

To make the file available to RDS SQL Server, the permission should be public access. Go to Permissions under the bucket, deselect Block all public access, then edit the Bucket policy to make the bucket publicly accessible. Use the Policy generator to generate the policy.

{
  "Version": "2012-10-17",
  "Id": "Policy1680878803300",
  "Statement": [
    {
      "Sid": "Stmt1680878801200",
      "Effect": "Allow",
      "Principal": "*",
      "Action": "s3:GetObject",
      "Resource": "arn:aws:s3:::mymssqldbbak/*"
    }
  ]
}

If we want to change the region of the bucket, we need to create a new bucket with the desired region and copy the files to the new bucket. To copy the files between buckets, run the following command in CloudShell:

aws s3 cp s3://mymssqldbbak/mymssqldb.bak s3://mymssqldbbak2/mymssqldb.bak

IAM

In IAM, select Roles and create a role. The Trusted entity type is AWS service by default. Under Use case, browse to RDS and select RDS - Add Role to Database, click Next, select the following policies:

AWSS3FullAccess
AWSBackupServiceRolePolicyForBackup
AWSBackupServiceRolePolicyForRestores

Click Next, enter a role name.

The role name will be used in RDS configuration later.

RDS

Create a database in RDS. Under Settings, enter the Master password. Under Connectivity, Public access should be Yes to allow remote access from the local client.

To enable the Restore feature, we need to create a Role in IAM (which we did earlier) and create an Option group in RDS.

In RDS, click Option groups in the left pane, create a group, then go back to the group list. Select the created group, click Add option, select SQLSERVER_BACKUP_RESTORE option, select the IAM role created in the last step. Select Immediately in the Scheduling. Click Add Option.

After creating the option group, we need to select the option group in the database configuration.

On the database page, under Manage IAM roles, add the S3_INTEGRATION feature to AWSBackupRestoreRole. (This step may not be necessary.)

In case we get the error that the recovery mode is not full: SIMPLE, we can go to the database configuration, under Availability & durability, and select No for Multi-AZ deployment.

We should now be able to restore the database.

Azure Data Studio (SQL Server Client on Mac)

Azure Data Studio is a SQL Server client on Mac. Connect to the AWS RDS SQL Server, open a New Query and run the stored procedure:

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