# Restoring SQL Server Databases in RDS from S3

Posted by : at

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:
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"
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: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.