Backup and restore for Azure SQL database using sqlpackage.

Palash Mathur 60 Reputation points
2025-01-09T07:03:12.3266667+00:00

I am using sqlpackage to export and import my Azure SQL Database as .bacpac files. My database contains:

  1. User-defined schemas and tables
  2. SQL Agent-related objects, such as jobs and the ##MS_JobAccount## user
  3. External users, such as service principals and custom logins

I need to export the entire database with all objects (including SQL Agent jobs, job-related accounts like ##MS_JobAccount##, and external users) and be able to import it into a new Azure SQL Database, ensuring that the newly created database is an exact replica of the original database — including all users, permissions, schemas, and job-related objects.

However, I’m encountering issues with SQL Agent-related objects and external users during the import process. Is there a direct and supported way using sqlpackage to achieve a full export and import of the Azure SQL Database, ensuring that no objects or users are lost in the process?

Would appreciate guidance on the recommended approach to achieve this, especially for databases that rely on SQL Agent-related objects and external users.

Azure SQL Database
{count} votes

1 answer

Sort by: Most helpful
  1. Olaf Helper 45,541 Reputation points
    2025-01-09T07:48:43.48+00:00

    Azure SQL Database don't have SQL-Agent and also don't support cross-database access, so it is not possible to use DacPac/BacPac nor your database in complete in your scenario.

    Use a VM with a full SQL Server, similar to an onpremise solution.


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.