question

TonyJK-1323 avatar image
1 Vote"
TonyJK-1323 asked TonyJK-1323 edited

Backup to BACPAC file

We are going to migrate our on-premise application to cloud (Azure).

Consultant asks us to backup the current database in BACPAC format instead of BAK.

May I ask
1) Is there any reason we use BACPAC instead of BAK ?
2) It appears that it will take longer to create BACPAC file than BAK though the size of BACPAC file is much smaller than BAK file. Is it correct ?
3) Do we need to resolve all errors when we export current on-premise database to BACPAC file ?

Thanks

sql-server-generalazure-sql-database
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

2 Answers

AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo commented

1) Bacpacs are smaller to transfer to Azure than BAKs as BAKs are a page by page copy of a database.
2) I would say time to create a native backup vs a bacpac is not the issue, the issue is the time it takes to transfer a native backup file to the cloud. Consider a 700 MB database that creates a BAK file with a Full + TLog and a size of 400 GB vs a bacpac of 23 GB of the same database.
3) I would say that is the part I like the most about bacpacs, you need to resolve errors / restrictions or remove problematic objects to be able to create a bacpac. For example, you may need to remove Windows logins.

· 4
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TonyJK-1323 avatar image TonyJK-1323 ·

Dear Alberto,

Thanks for your advice.

Is there any blog we can find the most common problem / errors for bacpac ?

Regards

0 Votes 0 ·
AlbertoMorillo avatar image AlbertoMorillo TonyJK-1323 ·

The errors are many and there are myriad of causes. We are here to help you in anty case. Allow me to leave you some articles: **here, here**.

My best suggestion is to use Data Migration Assistant before creating a bacpac. Use that free tool for performing an assessment.

Sorry for my late reply as I did not notice you added a comment.



1 Vote 1 ·
TonyJK-1323 avatar image TonyJK-1323 AlbertoMorillo ·

Hi,

We encounter problems when we export to BACPAC, is there any blog that mentions error for Export (Instead of Import) you suggest ?

We have tried DMA and there is no error. However, we get a number of the following errors
Error SQL71564 - IsMappedToWindowsLogin / The element [XXXX] has been orphaned from its login and cannot be deployed.

We need those Windows Login for daily operation, is there any suggestion ?

Thanks

0 Votes 0 ·
Show more comments
AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered AmeliaGu-msft commented

Hi TonyJK-1323,

In addition, SQL Server native backup are not supported on Azure SQL Database. Please refer to https://docs.microsoft.com/en-us/azure/azure-sql/database/features-comparison for more details.
And you can use the tools for this doc to help you migrate SQL Server databases to Azure SQL Database.

Best Regards,
Amelia

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 2
5 |1600 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TonyJK-1323 avatar image TonyJK-1323 ·

Dear Amelia,

From the requirement, can we tell whether they use "Azure SQL Database" or "Azure SQL Managed Instance" ?

Thanks

0 Votes 0 ·
AmeliaGu-msft avatar image AmeliaGu-msft TonyJK-1323 ·

Hi TonyJK-1323,

Thanks for your reply.
You can use T-SQL SELECT SERVERPROPERTY('EngineEdition') to check.
If it returns 8, it means Azure SQL Managed Instance.
Please check SERVERPROPERTY (Transact-SQL) for more details.

Best Regards,
Amelia



1 Vote 1 ·