Restoring SQL Server Databases in RDS from S3

Posted by : mransley at Nov 19, 2018

Category : aws

I often don’t play in RDS that much but every once in a while I jump back into it when a clients needs me to do so. I know that there is a way of restoring a SQL Server database from an S3 location, but for the life of me I could remember how it was done. For added bonus points I decided that I should try interacting with the database completely without a windows host.

The trick to ensure that the database can be restored is to assign an Option Group to the RDS Instance:

RDSOptionGroup:
  Type: "AWS::RDS::OptionGroup"
  Properties:
    EngineName: "sqlserver-web"
    MajorEngineVersion: "13.00"
    OptionGroupDescription: "DB Option Group for nonprod-test-appname"
    OptionConfigurations:
        - OptionName: SQLSERVER_BACKUP_RESTORE
          OptionSettings:
          - Name: IAM_ROLE_ARN
            Value: arn:aws:iam::123456789012:role/client-role-appname-rdsrestore
    Tags:
      - Key: Name
        Value: "nonprod-test-appname"

RDSInstance:
  Type: "AWS::RDS::DBInstance"
  Properties:
    AllowMajorVersionUpgrade: "False"
    AutoMinorVersionUpgrade: "True"
    CopyTagsToSnapshot: "True"
    Engine: sqlserver-web
    EngineVersion: 13.00.4466.4.v1
    DBInstanceClass: "db.t2.small"
    DBSubnetGroupName: !Ref RDSSubnetGroup
    MultiAZ: "False"
    OptionGroupName: !Ref RDSOptionGroup
    PubliclyAccessible: "False"
    Tags:
      - Key: "Name"
        Value: "nonprod-test-app"
    DBInstanceIdentifier: "nonprod-test-app"
    AllocatedStorage: "20"
    BackupRetentionPeriod: "35"
    MasterUserPassword: !Ref MasterUserPassword
    MasterUsername: DBUser
    StorageEncrypted: True
    StorageType: "gp2"
    VPCSecurityGroups:
          sg-0123456780abcdefg

Now, the bits to note above is the MajorEngineVersion of 13 is actually SQL Server 2016 and the OptionConfigurations points to an IAM role. It is this IAM role that RDS uses to actually perform the backup or restore operation. The IAM role needs to be similar to the following:

UserRoleAppRdsRestore:
  Type: "AWS::IAM::Role"
  Properties:
    RoleName: "client-role-appname-rdsrestore"
    AssumeRolePolicyDocument:
      Statement:
        - Action: [ "sts:AssumeRole" ]
          Effect: Allow
          Principal:
            Service: [ "rds.amazonaws.com" ]

UserPolicyAppRdsRestore:
  Type: "AWS::IAM::Policy"
  Properties:
    Roles:
        - !Ref UserRoleAppRdsRestore
    PolicyName: "APP-POLICY-RDSRESTORE"
    PolicyDocument:
        Statement:
          - Action:
            - "s3:PutObject"
            - "s3:GetObject"
            - "s3:GetObjectMetaData"
            - "s3:AbortMultipartUpload"
            - "s3:ListMultipartUploadParts"
            - "s3:ListBucket"
            - "s3:GetBucketLocation"
          Effect: Allow
          Resource:
            - "arn:aws:s3:::bucket_name"
            - "arn:aws:s3:::bucket_name/*"

Once this is deployed, log into an instance that has network access to the RDS instance and run the following commands (note: This assumes Docker is installed and running on the host):

docker run -it mcr.microsoft.com/mssql-tools

Then run the following to connect to the host:

sqlcmd -S nonprod-test-app.abcdefghijk.ap-southeast-2.rds.amazonaws.com -U dbusername

At this point you should be logged into the database, then type the following commands:

use master
go
exec msdb.dbo.rds_restore_database @restore_db_name='dbname', @S3_arn_to_restore_from='arn:aws:s3:::bucket_name/folder/db.bak'
go

To check the status of the restore run the following command inside the same window:

exec msdb.dbo.rds_task_status

And hopefully your database should be successfully restored.

About Michael Ransley

Hi, I am a Husband, Father of 3 children and Principal Data Engineer at Origin Energy. I am interested in many things - but technology is certainly one of them - both how to solve particular issues but also the effects that technology has on our society.

Website : https://michael.ransley.co