CODEWITHSUNDEEP
sql-serveramazon-web-servicesamazon-rds
Aaron
Aaron

Reputation: 135

Creating a duplicate SQL Server database on the same Amazon RDS

Background: I'm moving some clients to an AWS RDS SQL server instance. Each has two databases: one for their live data, and one for training data, which is a copy of their live data. The training database is kept up to date by routinely restoring it using the live database. Ideally these databases would be hosted on the same RDS instance, since they aren't that big and it would halve the cost of hosting.

The problem: Amazon has these limitations in their documentation:

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 a DB instance multiple times. That is, you can't restore a backup file to a DB instance that already contains the database that you are restoring. Renaming the database is not a workaround for this limitation.

So backing up the live database and restoring it to the training database as I normally would is out. Other than hosting two separate instances per client, what's my best option here?

Upvotes: 9

Views: 4502

Answers (2)

Danlance
Danlance

Reputation: 433

Great News:

The limitation listed previously in the RDS Documentation has been removed:

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 a DB instance multiple times. That is, you can't restore a backup file to a DB instance that already contains the database that you are restoring. Renaming the database is not a workaround for this limitation.

I have just tested this, and have been able to restore the same backup multiple times to different database names on the same RDS SQL Server without issue, using the msdb.dbo.rds_restore_database stored procedure as defined within the linked documentation.

Upvotes: 6

Aaron
Aaron

Reputation: 135

While possible to generate a script for a database, import it elsewhere, back it up and restore it back on the original instance, it wasn't a great workaround in my case. I've had to give up on using an RDS instance and go down the more traditional route of hosting multiple training databases on a Windows EC2 instance.

Upvotes: 2

Related Questions