What to know: Restoring Multiple Copies of a Database on AWS RDS SQL Server

When customers migrate application databases to Amazon Relational Database Service (RDS) SQL Server, they typically want to restore multiple copies of those database to segregate separate access domains such as production, quality assurance, testing, development, etc. Although the standard AWS recommendation is to use independent RDS instances, this can be an expensive and undesirable proposition. If you try to restore a backup file to a DB instance multiple times, you hit the following issue and troubleshooting roadblock:

Issue:

Database <database_name> cannot be restored because there is already an existing database with the same family_guid on the instance

Resolution:

You also 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. Instead, restore the backup file to a new DB instance (Translation: spend more $$$).

Fear not, here is one possible workaround. Using an on-premise SQL Server instance (or a SQL Server instance running on EC2), a SQL database can be exported into a BACPAC file and then imported back into that same SQL instance from that BACPAC file, thus creating “legal” copies of the same database that can now be restored into a single AWS RDS instance. For reference, a BACPAC file is a Windows file with a “.bacpac” extension that encapsulates a database's schema and data, whose primary use case is to move a database from one server to another. These files are a variant of data-tier applications (DAC), in the family of DAC packages (DACPACs). All DAC operations are supported starting with Microsoft SQL Server 2008 R2. Note that there are some exceptions, so be sure to review DAC Support for SQL Server Objects & Versions. For more information on Data-tier Applications refer to DAC concepts.

Need help implementing? Contact Cloudnexa today.

Tutorial: Restoring Multiple Copies of a Database on AWS RDS SQL Server

In this tutorial, we are going to duplicate the “AdventureWorks2016” sample database using SQL Server 2016. Open SQL Server Management Studio, select the database, select “Tasks”, and select “Export Data-tier Application…”.

• Click Next
  
  
  
  

• Select the “Save to local disk” radial button, choose a path and name for your “.bacpac” file. Click “Next”.
  
  
  
  

• Review the summary. Click “Finish”.
  
  
  
  

• Review the results, everything should read as a “Success”.
  
  
  
  

• Back in SQL Server Management Studio, right click on “Databases”, “Import Data-tier Application…”.
  
  
  
  

• Specify the BACPAC file to import. Click “Next”.
  
  
  
  

• Specify settings for the new copy of the database.
  
  
  
  

• Review the summary and verify the specified settings. Click “Finish”.
  
  
  
  

• Review the results, everything should read as a “Success”. Create full backups of the two databases and upload the “.bak” files to S3 (this post assumes you are familiar with, and have set up the prerequisites for AWS RDS SQL Server Native Backup and Restore).
  
  
  
  

• Connect to the target SQL Server RDS instance and restore the first database backup. To restore your database, you call the rds_restore_database stored procedure. The following parameters are required:

  @restore_db_name – The name of the database to restore.

  @s3_arn_to_restore_from – The Amazon S3 bucket that contains the backup file, and the name of the file.

  • Sample TSQL:

    exec msdb.dbo.rds_restore_database
    
    @restore_db_name='database_name', 
    
    @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';
    
    exec msdb.dbo.rds_task_status @db_name='database_name' 
    

  
  
  

  • Actual TSQL statement to restore our first database backup:

    exec msdb.dbo.rds_restore_database
    
    @restore_db_name='AdventureWorks2016',
    
    @s3_arn_to_restore_from='arn:aws:s3:::pa-rds-test/AdventureWorks2016.bak';
    

    
    
    

  • Actual TSQL statement to monitor the restore of our first database backup:

    exec msdb.dbo.rds_task_status @db_name='AdventureWorks2016'
    

• Once the “lifecycle” column reads “SUCCESS”, you can start the restore of the second database backup.
  

  • Actual TSQL statement to restore our second database backup:
    
    

    exec msdb.dbo.rds_restore_database
    
    @restore_db_name='AdventureWorks2016Copy1',
    
    @s3_arn_to_restore_from='arn:aws:s3:::pa-rds-test/AdventureWorks2016Copy1.bak';
    

  
  
  

  • Actual TSQL statement to monitor the restore of our second database backup:

    exec msdb.dbo.rds_task_status @db_name='AdventureWorks2016Copy1'
    

• Watch for the “lifecycle” column to read “SUCCESS”. Congratulations, you’ve restored multiple database copies to the same, single RDS instance.

Need help implementing? Contact Cloudnexa today.