Packager backup and restore process for SQL Server and Azure SQL Managed Instance

You can package your SQL scripts on the Azure SQL Managed Instance environment using Deploy Packager. There is a standard or ephemeral workflow based on the Connection Type to SQL Server.

Ensure that you configured Azure REST API settings for restore and backup operations. Otherwise, the Packager will fail with the message stating to set the values.

Using the standard workflow, the Deploy Packager performs the backup of the reference database as part of a standard operation. If packaging fails, the database is restored from that backup, and you can start future Deploy Packager jobs from a known good state.
Using the ephemeral workflow in Azure SQL Managed Instance, the ephemeral database is made by a REST API call to a point-in-time feature that makes a copy of REFDB.

For Azure SQL Managed Instance, a point-in-time operation creates a copy of the database on the same managed instance in the same subscription and region. This ephemeral copy will be used to perform all packaging phases: adding new scripts, testing rollbacks, forecasting, and deploying changes.

Backup creates a new instance (REFDB_EPHEMERAL) and Restore drops an existing instance (REFDB_EPHEMERAL).

If everything is successful, the REFDB is updated and the created copy is dropped. If there are issues during packaging, the REFDB is not affected and the copy created is dropped.

Standard Packaging Process

All operations are performed on the REFDB.
If the Convert SQL workflow is used, a restore occurs before the final Forecast and Deploy.

When a script is packaged using the Convert SQL, the restore is required before the final Forecast and Deploy.

If Packager fails after performing any operation on REFDB, REFDB is restored to its original state using a backup.

Ephemeral Packaging Process

All operations except for the final Forecast and Deploy are performed on a copy of the REFDB.
If files were packaged using the Convert SQL workflow, a previous ephemeral copy is dropped and a new ephemeral copy is created to perform Ephemeral Forecast and Deploy.
If Packager fails after performing any operation on the ephemeral copy of REFDB and that ephemeral copy, which was created before it, is dropped, the project state is restored back to a known state.

Point-in-time restore limitations

You can restore a database from one instance of Azure SQL Managed Instance to another only if two instances are in the same subscription and region. A cross-region and cross-subscription restore aren't currently supported.
You cannot restore all Azure SQL Managed Instance by using the point-in-time restore. You can restore only a database that is hosted on Azure SQL Managed Instance.
You need to be aware of the storage size of your SQL Managed Instance. Depending on size of the data to be restored, you might run out of instance storage.
You can use a point-in-time restore capability for a new, restored, or copied database from the time when the initial transaction log backup that follows the initial full backup is created. The first full backup is scheduled immediately after a new database is created or restored. This backup usually completes within 30 minutes, but it can take longer when the database is large. For example, the initial backup can take longer on a restored database or a database copy. After the first full backup, all further backups are scheduled and managed automatically. The exact timing of all database backups is determined by the Azure SQL Managed Instance service as it balances the overall system workload. You cannot change the schedule of backup jobs or disable them.