A common task on a SQL Server might be to copy a database into a new database. This could be on the same server or a different server.
On Amazon Web Service’s (AWS) Relational Database Service (RDS) this task becomes a little more complex to plan and execute. There are a few reasons for this:
- You don’t have access to the local file system outside of creating a database in the defaulted path.
- You do not have the needed permissions to run “Copy Database Wizard”
- Doing a “native” backup/restore using S3 would create a duplicate “family_guid” which is impossible to restore on your RDS instance without deleting the source database.
If you use MS SQL 2016 you can export/import a “Data-tier Application” which is stored as a “.bacpac” file. This is a schema and data native format package (not human readable). In reality it’s a .zip file, so if you open it in something like 7-Zip you can see the package contents. This package is going to be lacking items such as the physical index (the index configuration is intact) and the transaction logs. Therefore it tends to be smaller than an actual native backup.
Keep in mind all data is transmitted from the source machine (AWS RDS) to your workstation. If this is a larger database, you may wish to consider doing this from an EC2 instance to create a faster connection, provide for a larger drive workspace and potentially save on network costs.
Here are the steps to take to backup and then restore a “.bacpac” file. This example is done using AWS RDS, but the same steps would be taken for about any environment including local.
- Open Microsoft SQL Server Managment Studio (MSSMS) 2016 or better and connect to your database server.
- Right click the source database, go to tasks and “Export Data-tier Application”.
- Press “Next” to go to “Export Settings”. Enter a path on your local workstation to save the package to and press “Next”.
- After you press “Finish”, it will then begin the copy process from the source machine to your local machine.
- If you wish to import the database to another server, connect to it now via the MSSMS.
- Right-click the “Databases” group object and select “Import Data-tier Application”. Press “Next”.
- Specify the path on your local machine to the “.bacpac” file being imported. Then press “Next”.
- Specify a new database name. It can not be the name of an existing database. Press “Next” and then “Finish”.
- The new database will be created and imported.
Update 10/29/2018
It appears the “family_guid” issue is no longer an issue. I have not verified it as of yet. See https://aws.amazon.com/about-aws/whats-new/2018/10/amazon-rds-for-sql-server-enhances-backup-and-restore-capabilities/
Chris Tierney 