Creating a duplicate SQL Server database on the same Amazon RDS

7
votes

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?

2
sql-serveramazon-web-servicesamazon-rds
A very good reason for SQL Server clients to not move to AWS, this is a silly limitation and I think Amazon is not in a rush to solve it either, probably because it discourages us to use SQL Server. – M.Ali
I guess the only way to circumvent this issue would be to use Generate Scripts to script out your DB and import it to another DB instance of the same RDS instance. docs.microsoft.com/en-us/sql/relational-databases/scripting/… – Lasitha Petthawadu
If you use the training db for read only, consider using a read replica. This will also free you from continuously backing up production and restoring to training. – Raj
Just do it the same way you would do it with a traditional SQL Server, rather than using the provided Snapshot capability. – John Rotenstein
@JohnRotenstein - RDS instances only allow a backup to an S3 storage location in the same region, which is what OPs documentation notes apply to. The Snapshot capability is a cross-region feature. I wish we backup directly to disk as with usual SQL Server, but alas... the backup/restore options for RDS leave something to be desired. – jleach

2 Answers

4
votes

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.

2
votes

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.