AWS SQL Server RDS Native Backup Restore

AWS SQL Server Native Backup Restore

These are the steps I followed to enable Native Backup and Restores on AWS SQL Server RDS
             1. Created NativeBackupRestore Role and Enabled S3 Bucket Access
             2.  Created Custom Options Group and enabled to use the NativeBackupRestore Role
             3.  Modified existing instance to use custom options Group.
             4.  Successfully Backed up the Database from RDS Instance to S3 Bucket
             5.  Successfully Restored Native Backups on S3 on SQL Server RDS.

Backup Database to S3 Bucket

Restore Database from S3 Location

Tracking the Status of Tasks

To track the status of your backup and restore tasks, you call the rds_task_status stored procedure. If you don’t provide any parameters, the stored procedure returns the status of all tasks. The status for tasks is updated approximately every 2 minutes.

Cancelling Tasks

Limitations

You can’t restore a backup file to the same DB instance that was used to create the backup file. Instead, restore the backup file to a new DB instance. Renaming the database is not a workaround for this limitation.
You can’t restore the same backup file to the same DB instance multiple times. Renaming the database is not a workaround for this limitation.
You will encounter error like this.
Aborted the task because of a task failure or a concurrent RESTORE_DB request.
Database Reporting_07302016 cannot be restored because there is already an existing database with the same family_guid on the instance.

Option Group Properties

RDS Options Group

Creating Custom RDS Options Group

Modify RDS Instance

Modify RDS Instance

Option Group Name : sqlserver-web-optionsgroup
Option Group Description : SQL Server Web Options Group to enable native backup and Restore
Engine Name: sqlserver-web
Major Engine: database engine 12.00
Options : SQLSERVER_BACKUP_RESTORE
IAM_ROLE_ARN: arn:aws:iam::001234567890:role/service-role/NativeBackupRestore

Instance Configuration Details
EngineName : SQL Server Web 12.00.4422.0.v1
License Mode :  License Included
Created Time : February 29, 2016 at 11:55:34 AM UTC-6
Option Group : sqlserver-web-optionsgroup ( in-sync )

An Option Group that contains a list of configured Options (e.g. Oracle Enterprise Manager) that are attached to this DB Instance.
Parameter Group : default.sqlserver-web-12.0 ( in-sync )

NativeBackupRestore Policy

Policy ARN : arn:aws:iam::001234567890:policy/service-role/NativeBackupRestore
Policy JSON :

Make sure you have correct Trust Relationship for your Role:
RDS Trust Relationship

RDS Trust Relationship

Troubleshooting and Errors Encountered

Errors encountered while backing up database with enabling the feature
Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 74 [Batch Start Line 0]
Database backup/restore option is not enabled or is in the process of being enabled. Please try again later.

Solution:
Enabled native backup option through custom options group.

Errors with S3 Access Policy
Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup.
The specified bucket does not exist

Modified the access policy to ensure the role has access to S3 Bucket. I simulated S3 Access through policy access simulator.I had trailing / (forward slash) on ARN name which caused this error.

Restoring the RDS Backup on Same RDS Server with Different Database Name
Aborted the task because of a task failure or a concurrent RESTORE_DB request.
Database Reporting_07302016 cannot be restored because there is already an existing database with the same family_guid on the instance.
It seems you can’t restore the backup on RDS Server if the back up is from same RDS Server.

On-Premise Encrypted Backup Restore
Task execution has started. Cannot find server certificate with thumbprint ‘0xE82F1BCC5F20D65F31A0F4314828C31F72D161BD’.
RESTORE FILELIST is terminating abnormally. Aborted the task because of a task failure or a concurrent RESTORE_DB request.
RDS-Backups/Reporting_FULL_20160729_231356.bak:
S3 processing has been aborted Invalid attempt to read when no data is present.

Ensure your backup is not encrypted

Restoring SQL Server 2016 Backup
Task execution has started. The database was backed up on a server running version 13.00.1601.
That version is incompatible with this server, which is running version 12.00.4422.
Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
RESTORE DATABASE is terminating abnormally.

Make sure your backup is SQL Server 2014 or lower.AWS SQL RDS doesn’t support SQL Server 2016 yet.

Multiple Concurrent Restore Commands
Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 73 [Batch Start Line 22]
A task has already been issued for database: Reporting_07302016 with task Id: 5, please try again later.
USAGE: EXECUTE msdb.dbo.rds_restore_database @restore_db_name, @s3_arn_to_restore_from, [@kms_master_key_arn] @restore_db_name : Name of the database being restored. @s3_arn_to_restore_from : S3 ARN of the backup file used to restore database from. @kms_master_key_arn : KMS customer master key ARN to decrypt the backup file with.

Don’t issue same command multiple times . duh..

Permission Related Errors

Msg 229, Level 14, State 5, Procedure rds_restore_database, Line 1 [Batch Start Line 45]
The EXECUTE permission was denied on the object ‘rds_restore_database’, database ‘msdb’, schema ‘dbo’.

Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 58 [Batch Start Line 0]
Database backups can only be performed by members of db_owner or db_backupoperator roles in source DATABASE

Msg 229, Level 14, State 5, Procedure rds_backup_database, Line 1 [Batch Start Line 57]
The EXECUTE permission was denied on the object ‘rds_backup_database’, database ‘msdb’, schema ‘dbo’.

Random S3 File Download Error
Task execution has started. RESTORE DATABASE is terminating abnormally.
RDS-Backups/AdventureWorks.bak:
S3 processing has been aborted S3 read stream download failed. Encountered an error while downloading an S3 chunk.
Solution:
To re-run the restore job again.I didn’t find any other alternative through AWS Documentation.

Random Network Related Error
Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated BACKUP.:
Aborting S3 upload, waiting for S3 workers to clean up and exit .bak:
S3 processing has been aborted Write on “FF8C20D4BFAC-4C01-9E8E-7DE6C068CAD2” failed:
995(The I/O operation has been aborted because of either a thread exit or an application request.)
A nonrecoverable I/O error occurred on file “FF8C20D4-BFAC-4C01-9E8E-7DE6C068CAD2:” 995(The I/O operation has been aborted because of either a thread exit or an application request.).
BACKUP DATABASE is terminating abnormally.

Solution:
To re-run the restore job again.I didn’t find any other alternative through AWS Documentation. Also ensure your backup job is not triggered while you are doing the native backup.

Quiz :
http://ramblingsofraju.com/quizzes/

References :

Native backup Restore support Announcement
https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

Importing and Exporting SQL Server Databases
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Creating Roles
http://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-service.html

Amazon Resource Names (ARNs) and AWS Service Namespaces
http://docs.aws.amazon.com/general/latest/gr/aws-arns-and-namespaces.html

Modifying running SQL Server RDS Instance
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ModifyInstance.SQLServer.html

AWS Policy Access Simulator
http://docs.aws.amazon.com/IAM/latest/UserGuide/access_policies_testing-policies.html#policies_policy-simulator-using

Brent Ozar comment on AWS RDS native backup restore support
https://www.brentozar.com/archive/2016/07/holy-cow-amazon-rds-sql-server-just-changed-everything/

AWS Documentation for Options Group 
Amazon RDS uses option groups to enable and configure these features. An option group can specify features, called options, that are available for a particular Amazon RDS DB instance.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithOptionGroups.html

AWS Documentation for Parameter Group 
You manage your DB engine configuration through the use of parameters in a DB parameter group. DB parameter groups act as a container for engine configuration values that are applied to one or more DB instances.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

47 comments on “AWS SQL Server RDS Native Backup Restore
  1. Hi, very timely article. I’m having a problem restoring a backup from my standalone SQL instance.

    “Aborted the task because of a task failure or a concurrent RESTORE_DB request.
    Database XXXXXX cannot be restored because there is already an existing database with the same family_guid on the instance.”

    I guess at some point, one DB was built from another one. Not sure how I can restore now.

  2. Raju Venkataraman says:

    I wish AWS releases this functionality. Its one of the important limitation.

    You can’t restore a backup file to the same DB instance that was used to create the backup file. Instead, restore the backup file to a new DB instance. Renaming the database is not a workaround for this limitation.

  3. Prudhvi says:

    @Mario Rodrigues: You can restore it to another RDS instance, did you try that or do you have other restrictions that prevent you from doing it?

  4. Raju says:

    I couldn’t find any documentation on AWS SQL Serer RDS support for restoring on-premise encrypted database. It seems only option is to take un-encrypted backup before restoring it on AWS. Hope AWS provides this support.

  5. Prudhvi says:

    @Raju: That part is missing from the documentation currently. We will update that shortly.

  6. Raju Venkataraman says:

    @Prudhvi – Thank you ! .

  7. Mike Melamed says:

    When I try to execute the:

    exec msdb.dbo.rds_restore_database

    I’m getting the following error message:

    Msg 229, Level 14, State 5, Procedure rds_restore_database, Line 1
    The EXECUTE permission was denied on the object ‘rds_restore_database’, database ‘msdb’, schema ‘dbo’.

    I have tried to do several things but can’t seem to make the userid that I created as part of this AWS instance to acquire EXECUTE permission or become the superuser dbo…

    Any attempt to add my user to dbcreator role to grand EXECUTE permission results in:

    Cannot alter the server role ‘dbcreator’, because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)

  8. Raju Venkataraman says:

    I have encountered similar errors while doing these backup / restore operations.

    Msg 229, Level 14, State 5, Procedure rds_backup_database, Line 1 [Batch Start Line 57]
    The EXECUTE permission was denied on the object ‘rds_backup_database’, database ‘msdb’, schema ‘dbo’.

    Msg 4613, Level 16, State 1, Line 4
    Grantor does not have GRANT permission.

    Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 58 [Batch Start Line 0]
    Database backups can only be performed by members of db_owner or db_backupoperator roles in source database

    One workaround I used is to create a user in the source database assign the role of db_backupoperator.

  9. Mike Melamed says:

    Thanks for the advice. I actually got a little bit further by creating a brand new AWS DB instance and using as my master username something other than ‘sa’. It’s possible that ‘sa’ was clashing with a local ‘sa’.

    But now my new hangup is this:

    Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 37
    Database backup/restore option is not enabled yet or is in the process of being enabled. Please try again later.
    USAGE:
    EXECUTE msdb.dbo.rds_restore_database @restore_db_name, @S3_arn_to_restore_from, [@KMS_master_key_arn]

    @restore_db_name : Name of the database being restored.
    @S3_arn_to_restore_from : S3 ARN of the backup file used to restore database from.
    @KMS_master_key_arn : KMS customer master key ARN to decrypt the backup file with.

  10. Raju Venkataraman says:

    In fact I used master username and password for the initial Try. After that. I setup the login and granted following permission which worked well.

    I think you are encountering these errors because you probably didn’t setup your parameters group and enabled the backup / restore option.

  11. Mike Melamed says:

    I didn’t see that in the documentation (setting up param groups to enable backup/restore option). Can you please point me in that direction? I understand about the desire to create a different user and granting permissions to it but not sure that that is the root issue here now…

  12. Raju Venkataraman says:

    Based on AWS Announcement link :

    Then add the SQL_SERVER_BACKUP_RESTORE option, specify (or create) an IAM role to allow RDS to access S3, point to a bucket, and (if you want) specify and configure encryption:

    https://aws.amazon.com/blogs/aws/amazon-rds-for-sql-server-support-for-native-backuprestore-to-amazon-s3/

    Hope this helps!.
    Thanks,

  13. Mike Melamed says:

    I did indeed create the SQL_SERVER_BACKUP_RESTORE option (from the Option Groups) menu when I was configuring my instance and I linked that up to my S3 bucket. But you had mentioned setting up a Parameters Group before. Is there anything that needs to be done in the Parameters Group to unlock the Backup/Restore functionality?

  14. Raju Venkataraman says:

    Sorry for the Confusion. I meant Option Group. I just followed these steps and I did successfully backup and restore my databases. I had minor issue with S3 bucket permissions and I was able to resolve that after going through IAM Policy simulator.

  15. Mike Melamed says:

    I wish I could say the same. I’m not having luck. Which version of SQL Server did you select? I’m trying to set up SQL Server 12.0.

    Also the thing that you suggested earlier didn’t work either, I am unable to create a userid because my master user account is restricted, so that when I try to create a new user with the command:

    CREATE USER [dba_user] FOR LOGIN [dba_user]

    I get an error:

    Msg 15007, Level 16, State 1, Line 1
    ‘dba_user’ is not a valid login or you do not have permission.

  16. Raju says:

    I think you probably didn’t create the Login. Login needs to be in place before creating database User.

  17. Mike Melamed says:

    I was able to create the user. I just had to grant dba_user one more permission (the ability to create any database). This was required to be able to do the restore:

    USE master
    GRANT CREATE ANY DATABASE TO dba_user

    However although I was able to create the user, when I logged in with this user and attempted to do a restore, I got the same error message as before:

    Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 37
    Database backup/restore option is not enabled yet or is in the process of being enabled. Please try again later.

    So back to square 1 for now.. If I have some time tomorrow I’ll try to maybe start to do everything from scratch, maybe I missed something.

    Are you successfully using the latest: SQL Server SE 12.0 ?

  18. Raju says:

    In Lab we had developer edition and restored it on RDS. I hope you can get it working.

  19. Mike Melamed says:

    I figured it out. After the DB instance is created, I had to go back into it, Modify the instance, and under Database Options, modify the Option Group from ‘default’ to the ‘nativeBackup’ group that I created to perform the backups. Also had to select ‘Apply Immediately’. After saving, it took about 5 minutes for the system to modify itself, and after that I was able to perform the restore operation.

    Thanks for your help.

  20. Raju Venkataraman says:

    Glad you got it working.

  21. kashi says:

    I am getting this error when backup up to bucket. using master account.

    Aborted the task because of a task failure or an overlap with your preferred backup window for RDS automated backup. Access Denied

  22. Raju says:

    Hey Kashi,
    I had similar error when I was trying to restore and Modified the access policy to ensure the role has access to S3 Bucket. I simulated S3 Access through policy access simulator.I had trailing / (forward slash) on ARN name which caused this error.

    I would recommend to simulate the permissions through IAM Policy simulator and see right permissions are assigned.

    Thanks,

  23. kashi says:

    can we restore multiple striped backup to RDS. I tried but for error too many parameter. my backup file is striped into 3 files and I would like them to be restored to RDS. Any help be great.

    Msg 8144, Level 16, State 2, Procedure rds_restore_database, Line 0
    Procedure or function rds_restore_database has too many arguments specified.

  24. Mahendra says:

    Aborted the task because of a task failure or a concurrent RESTORE_DB request.
    RESTORE_DB task is unable to locate the backup file in S3. Try again and specify an existing backup file.

  25. Raju Venkataraman says:

    Hello Mahendra ~
    Were you able to resolve the issue?

  26. Mahendra says:

    No @ Raju Venkataraman i am looking for the solution…

  27. Raju Venkataraman says:

    @Mahendra .. Did you verify the S3 bucket access?. S3 Policy simulator comes handy to verify the access. I had similar error when I was trying to restore and I used Policy simulator to troubleshoot. Hope this helps.

  28. David Eichner says:

    I am also getting the error that @Mahendra is getting. We followed all of the instructions in setting up the Option Group to have access to the S3 folder. Yet, we are still getting the error “unable to locate the backup file in S3”.

  29. Raju Venkataraman says:

    Hello David ,
    Did you simulate the policy attached to your S3 bucket with policy simulator. This helps to identify some of the permissions related errors.
    Thanks,

  30. Dmitriy Klyushin says:

    Hello! I’m stuck and need some help ))) I’m using AWS free tire t2.micro
    I’m trying to restore database from s3 bucket.

    I use this command in SSMS2017:

    exec msdb.dbo.rds_restore_database
    @restore_db_name=’UserDB’,
    @s3_arn_to_restore_from=’arn:…………………/UserDB.bak’;
    Result: lifecycle – created. Task created successfully.

    But when I refresh my Object Browser I can’t find the database UserDB …. Why? How can I see it ? Thanks!!!

  31. Raju Venkataraman says:

    Hello Dmitriy,
    What error do you get when you execute “exec msdb.dbo.rds_task_status” ?. Most likely your RDS Instance doesn’t have access to the S3 Bucket you are trying to access. You need to make sure the OPTIONS Group is setup correctly with Appropriate ROLE which has access to S3 Bucket. You can verify your policy using Policy generator.
    Hope this helps.

  32. Dmitriy Klyushin says:

    Hello Raju! Thanks for your respond!

    I don’t get any errors! Only status “created”.

    some screenshots of my configs:
    role and policy config: http://i.imgur.com/MwwNKLp.png
    option-group config: http://i.imgur.com/YoS7oBe.png
    database instance config: http://i.imgur.com/ZrzSRXs.png
    bucket config: http://i.imgur.com/5rep7Zs.png

    screen of the query I make: http://i.imgur.com/VBXMTfU.png
    as you see there’s no “UserDB” database after restore and object explorer refresh

    screen of the query I made to create a new database and try to backup it to s3 bucket – http://i.imgur.com/cE0SknV.png
    it also returns “Created” but it won’t show up in my bucket as well …

    So I’m confused, I suppose I did all the configs as you mentioned in your reply but I still fail to restore/backup database.

    Any ideas? Thanks!

  33. Raju Venkataraman says:

    Hello Dmitriy,
    Can you please verify what you have in Trust Rrelationships?.
    You should have something similar to this.

    Trusted entities
    The following trusted entities can assume this role.
    Trusted entities
    The identity provider(s) rds.amazonaws.com

    If trust relationships are setup correctly , then it comes down to storage. Do yo have enough storage in your instance?.

    The other thing to verify is whether S3 Access is restricted to any specific VPC Endpoint?. Other than , that your configurations looks pretty much exactly what I have in my end and it works for me.

    Hope this helps.
    Thanks,

  34. Raju says:

    Are you restoring the database which already exists in RDS instance with different name?. AWS Doesn’t support that option and we encountered this issue in the past.

  35. Niran says:

    Hi,

    I have question once we restore the Database, do we need to set the default option group for the RDS SQL server?
    Or we can leave the option group what we created for restore the Database. I have some issue with Application talking to Database.(MS SQL)
    Thanks,
    Niran

  36. Raju Venkataraman says:

    Niran,
    You can leave the default option group which also provides ability to customize your options specific to your environment.
    If you are having issues with Application connecting to the RDS Instance, most likely root cause is something with networking ( VPC , NACL,Security Groups ). I would first look into security groups and subnet configurations.

  37. Laura Vargas says:

    I have a question related to RDS tasks. I’m trying to backup a db using the msdb.dbo.rds_backup_database sp. I get an error message:
    Msg 50000, Level 16, State 0, Procedure rds_backup_database, Line 104
    A task has already been issued for database: mcom_saivian with task Id: 222, please try again later.
    It seems like a previous bakup failed, so I tried to cancell that task with the sentence:
    exec msdb.dbo.rds_cancel_task @task_id=222;
    After that, when I check the task status for my db, the “lifeclycle” column sais CANCEL_REQUESTED, but the % complete never changes. So, I cannot backup my db, nor cancel the previous backup request.
    Any idea how to solve this??
    Thanks!!

  38. Raju Venkataraman says:

    Hello Laura,
    Have you had opportunity to verify the Backup/Restore Role and policy attached with Role has appropriate permissions to access S3 Bucket?. We had similar issues last year and some of them were triggered by incorrect policies and role permissions.
    Thanks,

  39. Fredy says:

    Hello Raju, I am stuck and need your help.

    I have created a SQL Server Express 13.00.4422.0.v1 RDS Instance and an Amazon S3 within the same AZ. I backup my test database (TestDB) from my laptop running SQL Server Express 12.0.2269. I configure the option group to allow SQLSERVER_BACKUP_RESTORE. The database instance class is db.t2.micro. I created a bucket in Amazon S3 named DBRestore and upload my backup file to it. I executed the rds_restore_database stored procedure
    exec msdb.dbo.rds_restore_database
    @restore_db_name=’TestDB’,
    @s3_arn_to_restore_from=’arn:aws:s3:::DBRestore/TestDB.bak’

    The rds_task_status returns the following error

    [2017-10-20 21:14:24.783] Aborted the task because of a task failure or a concurrent RESTORE_DB request.
    [2017-10-20 21:14:29.633] Error making request with Error Code Forbidden and Http Status Code Forbidden. No further error information was returned by the service.

    Have you encountered this error before?

    Thanks.

  40. Raju Venkataraman says:

    Hello Fredy,
    Did you reboot the RDS instance after applying the Role?. We haven’t encountered exact issue but we had something similar. The Role we used didn’t have access to the S3 Bucket and we had incorrect S3 path. I would ensure RDS instance has enough storage allocated and RDS role has permissions to access the S3 bucket.
    So also encountered intermittent networking issues. I hope this helps. If this doesn’t work, feel free to reach out to me via email with further error details.
    Thanks,

  41. Fredy says:

    Raju, thank you for pointing me in the right direction. I thought I my IAM role was setup correctly but when I reviewed the policy it turns out that it does not have access to the correct folder as I rename the folder from “DB Restore” to “DBRestore” after the fact and the policy does not automatically sync. I manually change the folder name within the policy and it is now working like a charm. Thanks again. You save me a lot of time and heartache…

    Cheers.
    Fredy

  42. Laura Vargas says:

    Hi, Raju.

    Just for you to know. The root cause of my problem was db space. The server had about 5% of space left, and that was preventing from cancelling the previous task. Once we made some space, the cancel / backup operations could be completed.
    Thanks !!
    Laura

  43. Doug K says:

    Hi Raju,

    I want to know if something is possible. I have been searching all day and either I am not using the correct terms or it is not possible. I am backing up a RDS database to S3 with no issues. What I am trying to do now is have a circular backup sets going. So, the first backup is called backup1.bak, second backup is called backup2.bak, then backup3.bak. So, when the 4th backup happens it overwrites backup1.bak or deletes backup1.bak and creates backup4.bak. I only want 3 backups in the folder but I don’t want it to overwrite the same one, in case 1 of the backups gets corrupted I have 2 others to choose from.

    Is this possible with the RDS?

    Thank you in advance
    Doug

  44. Raju Venkataraman says:

    Hello Doug,
    I wish I could be more useful but we haven’t setup circular backup sets that you are looking for. What we currently have in our prod uction environment is daily snapshots and daily backups to S3 buckets with timestamp file names and cronjob to delete files older than 2 Weeks.

  45. Mujahid Iqbal says:

    Hello,

    I am getting same error as well i.e.

    [2017-10-20 21:14:24.783] Aborted the task because of a task failure or a concurrent RESTORE_DB request.
    [2017-10-20 21:14:29.633] Error making request with Error Code Forbidden and Http Status Code Forbidden. No further error information was returned by the service

    One of the things that I have observed in role screen is the warning against GetObjectMetaData under Unrecognized Actions.

    Do you have any idea of this??

  46. Mujahid Iqbal says:

    Well there was a problem in ARN path. It has been solved.

  47. Ásgeir says:

    What was the problem with the arn path ? I’m having the same issue.

Leave a Reply

Your email address will not be published. Required fields are marked *

*