.BAK and bacpac/dacpac questions - Azure

Akash Bhot 26 Reputation points
2021-08-23T20:34:44.407+00:00

hello

Please help me answer below questions :

.BAK backups

1) Can I generate .BAK backup from SQL MI and restore it another SQL MI cross subscription

1) Can I generate .BAK backup from SQL MI and restore it another SQL Virtual machine cross subscription

bacpac/dacpac :

1) Can I generate bacpac/dacpac backup from SQL MI and restore it another SQL MI cross subscription

2) Can I generate bacpac/dacpac backup from SQL MI and restore another SQL Virtual machine cross subscription

3) Are bacpac/dacpac TDE enabled ?

Azure SQL Database
0 comments No comments
{count} vote

Accepted answer
  1. Alberto Morillo 34,041 Reputation points MVP
    2021-08-24T15:39:43.853+00:00

    About .BAK native backups, yes you can restore them on another SQL MI cross subscription. However, if you want to restore a .BAK native backup to an Azure SQL VM or SQL Server (on-premise), the my suggestion is to use bacpacs instead because of the issues explained on this article.

    About the bacpacs/dacpacs, you can restore a bacpac on another SQL MI instance and on another SQL Server instance (Azure VM or on-premises).

    About TDE and bacpacs, when you export a TDE-protected database as bacpac, the exported content of the database isn't encrypted. This exported content is stored in unencrypted BACPAC files. Be sure to protect the BACPAC files appropriately and enable TDE after import of the new database is finished.


1 additional answer

Sort by: Most helpful
  1. Oury Ba-MSFT 19,501 Reputation points Microsoft Employee
    2021-08-31T16:21:36.38+00:00

    Hi @Akash Bhot Thank you for posting your question.
    Can I generate .BAK backup from SQL MI and restore it another SQL MI cross subscription
    Yes, you can as Morillo mentioned above.
    Can I generate .BAK backup from SQL MI and restore it another SQL Virtual machine cross subscription
    yes, you can generate .BAK backup from SQL MI and restore it another SQL Virtual machine cross subscription

    Are bacpac/dacpac TDE enabled ?*
    When you export a TDE-protected database, the exported content of the database isn't encrypted. This exported content is stored in unencrypted BACPAC files. Be sure to protect the BACPAC files appropriately and enable TDE after import of the new database is finished as Morillo mentioned above.

    Additional Info :
    BAK files that contain multiple backup sets can't be restored.
    .BAK files that contain multiple log files can't be restored.
    Backups that contain databases bigger than 8 TB, active in-memory OLTP objects, or number of files that would exceed 280 files per instance can't be restored on a General Purpose instance.
    Backups that contain databases bigger than 4 TB or in-memory OLTP objects with the total size larger than the size described in resource limits cannot be restored on Business Critical instance. For information about restore statements, see RESTORE statements.

    Regards,
    Oury

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.