How to import .backpac file in AWS RDS MSSQL

To import .backpac file into AWS RDS we need to convert bacpac file to .bak file, Please follow below steps to convert .backpac to .bak

Step 1- Converting bacpac file to .bak file

1. Create an EC2 instance with SQL Server or if you already have an on premise SQL Server environment you can use it as well. With SQL Server on EC2 you have full access to the SQL Server engine and can have a user with sysadmin permissions.
2. Import the bacpac file to the instance used in step 1.
3. Create an S3 bucket if you don’t have one. Please refer to link [3] to see the steps on how to do this in the reference section below.
4. When done importing the bacpac file, take a backup of the database. The backup file will be a .bak file. Copy the .bak file to the S3 bucket mentioned/created in step 3.
5. Next you going to restore the backups file from S3 to RDS, but before you can do that you need to add the ‘SQLSERVER_BACKUP_RESTORE’ parameter option.

Moving ahead, for your reference I have also mentioned below some simple steps on how to configure SQL Server Native Backup/Restore and then perform the restoration of the backup files created in step 4 in the RDS instance.

Step 2- Create an S3 bucket :

1- Sign in to the AWS Management Console and open the Amazon S3 console at https://console.aws.amazon.com/s3/.

2- Choose Create bucket.
The Create bucket page opens.

3- In Bucket name, enter a DNS-compliant name for your bucket.
Note : After you create the bucket, you can’t change its name.

4- In Region, choose the AWS Region where you want the bucket to reside.

5- In Bucket settings for Block Public Access, keep the values set to the defaults.

6- Then, Choose Create bucket.

Creating a bucket : https://docs.aws.amazon.com/AmazonS3/latest/gsg/CreatingABucket.html

–

Step 3- Create an Option Group:

1- Sign in to the AWS Management Console and open the Amazon RDS console.
2- In the navigation pane, choose Option Groups.
3- Choose Create Group.
4- In the Create Option Group dialog box, do the following:
-> For Name, type a name for the option group that is unique within your AWS account. The name can contain only letters, digits, and hyphens.
5- For Description(optional), type a brief description of the option group. The description is used for display purposes.
6- For Engine, choose the “DB Engine” and the correct “Edition” of your RDS instance on which you are going to apply the option group.
7- For Major Engine Version, choose the major version of the DB engine that you want.
8- To continue, choose Yes, Create.

Step 4- Add Option To Option Group:

1- Sign in to the AWS Management Console and open the Amazon RDS console
2- In the navigation pane, choose Option Groups.
3- Select the option group that you want to modify, and then choose Add Option.
4- In the Add Option dialog box choose the option that you want to add. In your case this will be the “SQLSERVER_BACKUP_RESTORE” option.
5- If you already have an IAM role then you can select that OR you can always create a new IAM role by typing a name for the role. (We recommend creating a new IAM role so that your IAM role has the required privileges).
6- Choose your S3 bucket, or create a new S3 bucket.
7- To enable the option on all associated DB instances as soon as you add it, for “Apply Immediately”, choose Yes. If you choose No (the default), the option is enabled for each associated DB instance during its next maintenance window.
8- Click Add Option.

Step 5 – Associate the option group with the RDS instance:

1- Sign in to the AWS Management Console and open the Amazon RDS console.
2- In the navigation pane, choose DB Instances, and then select the DB instance that you want to modify.
3- Choose Instance Actions, and then choose Modify. The Modify DB Instance page appears.
4- Under the Database Options section select the option group you created in the Option Group dropdown.
5- Click Continue.
6- To apply the changes immediately, select Apply Immediately.
7- RDS instance would go into modification state and takes some time to take effect. Once RDS is available you can try performing backup/restore.

You can refer the AWS documents mentioned in references to have more insight on RDS SQL server native backup/restore feature:[I],[II]

[II] Importing and Exporting SQL Server Databases https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.html

Once the above steps are done, now please log on to your RDS instance, and create a new query, RDS uses native stored procedures to do restores. To restore run the below query using the master account, please note that the below is just an example of the syntax

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';

References –
[1] – ]RDS SQL Server permissions: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.FeatureSupport.UnsupportedRoles
[2] – Permissions required to import bacpac: https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/import-a-bacpac-file-to-create-a-new-user-database?view=sql-server-2017#permissions
[3] – How to create a S3 bucket: https://docs.aws.amazon.com/AmazonS3/latest/user-guide/create-bucket.html