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:
And hopefully your database should be successfully restored.