It’s common for large data analytics solutions to have multiple staging environments such as development, testing, and quality assurance to ensure stable feature releases to production. When using SQL analytics capabilities within Azure Synapse Analytics (formerly known as SQL Data Warehouse), it’s recommended to decouple these environments in separate databases to ensure these environments do not impact one another and periodically refresh these staging environments with production data. Oftentimes this refresh can be simply achieved through the database backup and restore capabilities which can be done cross or in-place a logical SQL Server. Many developers have multiple subscriptions or resource groups hosting each of these environments and would like to also refresh by restoring environments across these two boundaries. This blog outlines how to simply refresh and sync your staging environments with production data when environments reside in different Azure subscriptions or resource group.
1. Navigate to the portal of your SQL pool and create a database restore point
2. Initiate a cross server restore using the newly created restore point
3. Navigate to the logical server of your newly restored data warehouse and select the Move operation in the command bar of your logical server. Note the Move capability is only available at the logical server level which will move all databases under your server.
This process can be operationalized and automated in Azure DevOps leveraging PowerShell. Documenation links can be found here: