Restore a backup file on AWS RDS SQL Server from S3 bucket

Question

I am able to successfully get the database backup from SQL Server instance on AWS with the following command to an S3 bucket:

-- Backup databases - MyDB
exec msdb.dbo.rds_backup_database 
       @source_db_name='MyDB',
       @s3_arn_to_backup_to='arn:aws:s3:::mybucket/MyDB.bak',
       @overwrite_S3_backup_file=1;

While Restoring the same backup to a different SQL server instance within the same region using the following command, it's not working:

    -- restore databases - MyDB
EXEC msdb.dbo.rds_restore_database 
        @restore_db_name='MyDB', 
        @s3_arn_to_restore_from='arn:aws:s3:::mybucket/MyDB.bak';

i am getting the following error when checked the task status with exec msdb.dbo.rds_task_status @db_name='MyDB'

[2017-05-19 19:22:22.127] Aborted the task because of a task failure or a concurrent RESTORE_DB request.
[2017-05-19 19:22:22.150] Error making the request with Error Code Forbidden and Http Status Code Forbidden. No further error information was returned by the service.

I have done this backup and restore on multiple DB instances till now and never seen this kind of error. The S3 bucket and the SQL Server instance where the .bak file needs to be restored are in the same region, the assigned option group role has all the required permissions set on the S3 bucket as well. Below is the Options group Role policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": [
                "arn:aws:s3:::mybucket"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObjectMetaData",
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
            "Resource": [
                "arn:aws:s3:::mybucket/*"
             ]
        }
    ]
}

hakuna hakuna · Accepted Answer · 2017-05-22T13:39:24

The problem is with the backup(.bak) files, when they are not transferred properly or missing some meta data information, this error shows up. Initially i had transferred the files from one AWS account (ap-northeast-1) to another AWS account (us-east-1) in different regions using the command

aws s3 sync s3://account1bucket s3://account2bucket --source-region ap-northeast-1

But looks like that did not do the job properly. Now, i have manually downloaded the files from the Source S3 bucket and uploaded to the destination S3 bucket, it started working now.

Probably this might fix some of the issues for transferring the bucket contents using the AWS CLI: https://github.com/aws/aws-cli/pull/1122

Restore a backup file on AWS RDS SQL Server from S3 bucket

6 Answers