Amazon RDS - Common Issues with native SQL Server backup and restore

Category : Articles Published : November 15, 2017 User Rating : 4.5 Stars      Views : 4 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.




Summary

In my introductory article (link) I outlined details of how to create and restore SQL Server backup files to and from on-premise SQL Servers.

In this follow up article I’m going to discuss some common issues I’ve experienced when both backing up and restoring RDS databases.

Amazon Web Services (AWS) have also published a useful article listing some of the limitations here : http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Issue 1 : RDS Server not Configured Correctly

The following error can occur when running the rds_restore_database stored procedure.
Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 38
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.
The above error occurs if the SQLSERVER_BACKUP_RESTORE option has not been applied to the server (see the original article for details of how to do this).

Issue 2 : Cannot Restore a Database and Replace an Existing Database

The following error can also occur when running the rds_restore_database stored procedure.
Msg 50000, Level 16, State 0, Procedure rds_restore_database, Line 71
Database SQLMatters already exists. Cannot restore database with the same name.

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.
In this case this has occurred because it’s not possible to overwrite an existing database while restoring a backup, unlike a normal SQL Server restore. In other words, there’s no equivalent of the ‘WITH REPLACE’ option in RDS. Instead you have to delete the database yourself, before running the restore command above.

Issue 3 : Trying to Restore more than One Database from the Same Source

Sometimes the following error is reported when checking on the progress of the restore using the rds_task_status stored procedure (the restore appears to start correctly as no error is returned by rds_restore_database).

[2017-11-13 15:32:22.277] Aborted the task because of a task failure or a concurrent RESTORE_DB request.
[2017-11-13 15:32:22.310] Task ID 22 (RESTORE_DB) exception: Database SQLMattersNew cannot be restored because there is already an existing database with the same file_guids on the instance.


This error occurs when trying to create more than one database on an RDS server from the same backup. This will occur even if the new database has a different name, so renaming the database is not a workaround.

The only way (that I know of) to create a new set of file GUIDs is to create a brand new database from scratch, i.e.one that isn’t created from a restore. Once the new database is created you’d then need to copy schema, data, stored procs, permissions etc using some sort of scripting, e.g. the ‘Generate Scripts’ option in SSMS. Alternatively there is also the AWS Data Migration Service (DMS).

Issue 4 : Exceeding the Limit of 30 Databases per Instance

RDS limits you to 30 user databases per instance. If you try and exceed that limit then you’ll get the SQL error below (if trying to create the database using SQL).
Msg 50000, Level 15, State 1, Procedure rds_create_database_trigger, Line 33
Database creation would exceed quota of 30
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
If you try and do this via the GUI then you’ll get a similar error : RDS Databases Limited to 30 per instance


Related Articles

The following articles may also be of interest :

Link back to this article : https://www.sqlmatters.com/Articles/Amazon RDS - Common Issues with native SQL Server backup and restore.aspx

Keywords

SQL 2016, 2014, AWS, RDS, backup, restore


Comments
Post by AWS training in hyderabad on Tue 10 Nov 2020 06:44. Report Inappropriate Post
Website : https://aditidigitalsolutions.com/aws-training-hyd
very informative article post. much thanks again
Post by AWS training in hyderabad on Mon 08 Feb 2021 09:47. Report Inappropriate Post
Website : https://aditidigitalsolutions.com/aws-training-hyd
Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
Post by Carlos on Fri 11 Jun 2021 23:31. Report Inappropriate Post

I can run the rds_restore_database command but the restored database does not show up in the "Databases" group. Where is it?
The 'task_progress' column in the results is '0'. What does that mean?

Post a comment   No login required !

 
Name :   Email :   Website :  
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered