Often we need a fresh copy of the production database in another environment (eg DEV/TEST/UAT).
Previously this was a tedious task involving getting a backup file, copying it to another location, restoring the database. Here is a solution to automate this process using Azure Pipelines.
User Story
Given a production database exists in subscription 1
When we do a release of the Azure Pipeline named ‘Refresh Database – DEV’
Then an copy of production is available in the DEV environment in subscription 2
And permissions are correct for the DEV environment
Pipeline Overview
For each environment that you wish to restore into create an Azure Pipeline with 3 stages.
Create variable groups that are scoped to specific stages
Each variable group contains deployment credentials that the specific stage will require to perform operations within the specific Azure Subscription.
Task 1 – Export
Create a PowerShell task to run a script and pass it the information for the production environment.
This runs against the production environment and creates a blog storage container that holds the exported BACPAC
View code for script production-export.ps1
Results in production once this script run should show the database BACPAC export
Task 2 – Import
Under the ‘import’ stage create a task that will import the BACPAC from the storage container in the production subscription. This uses both production and the environment credentials.
View code for script production-import.ps1
Task 3 – Sanitise
Create a 3rd task in the ‘Sanitise’ stage.
This will scramble any information you do not want in that environment (eg emails).
Also remove any Production SQL user account and replace them with environment specific
View code for script sanitise
TADA!
Running the pipeline now copies the database to the DEV environment. Typically after this will run a software build which will automatically apply schema changes currently in DEV in the database. Happy restoring!
Top comments (5)
Good post. Realize this is an old post but do you have any thoughts on selective restore of a subset of production data into lower environments. Restoring the entire production database into lower environments is too expensive. We also have many production databases that would need to stay in sync in lowers with regard to the subset.
Hi. This looks like it could help me, but when i click on the pic thumbnails I get 404 errors. I think I need a better view of those thumnails. Could you update the post pretty please?
@chris_mcklet Very good blog friend, I have a question, what do you use in the git _arm templates repository, I don't understand that part much I have some doubts there, thank you.
Hi - that folder contained the ARM templates for the original solution. The scripts to action this blog post were stored in a folder under it called 'scripts'. Hope that answers your question?
Hi, Chris McKelt, Nice writing! Thanks for the blog.
Do you have a similar script for MySQL, Cosmos, and Mongo?