Packager backup and restore process for SQL Server and Azure SQL Database

Azure SQL Database support is available in 7.9 and later releases

You can package your SQL scripts on the Azure SQL Database environment using Deploy Packager. There is a standard or ephemeral workflow based on the Connection Type to SQL Server. 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.

For non-Azure Databases, the Deploy Packager uses your installation of sqlcmd to create a restore file for your REFDB during packaging. The files are stored on the database server.

The default location of a backup is stored in the BackupDirectory registry key under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.n\MSSQLServer. However, you can specify the path to your backup files with the databaseBackupRestoreLocation attribute. For more information, see Managing Database Backup and Restore and Using the Required deployPackager.properties File.

For Azure SQL Database, the Deploy Packager creates a copy of the database at the beginning of packaging. 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 of SQL Database (REFDB_EPHEMERAL) and Restore drops an existing instance of SQL Database (REFDB_EPHEMERAL).

If everything is successful, the REFDB is updated and the copy created 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 prior to 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 prior to it, is dropped, the project state is restored back to a known state.
If Packager does not fail after performing any operation on the ephemeral copy of REFDB, the copy is dropped, Forecast is performed again, and changes are deployed to REFDB.