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 :
