Copy production database to staging on Azure DevOps
I’m building build and release pipeline on Azure DevOps for one of my projects. We want to automate testing and deployments to staging environment. At staging environment we want to use copy of production database to make sure that testers are working with latest data. Here’s how to make Azure SQL database copy on Azure DevOps using Azure PowerShell.
My scenario
What I’m building is shown on the following image. It’s a build and release pipeline on Azure DevOps and its goal is to get latest accepted changes to staging environment for final user testing before deploying new features to production.
I don’t go through all steps in this post. I have post ASP.NET Core code coverage reports on Azure DevOps showing how to run unit tests and check code coverage on Azure DevOps. My focus in this post is on making copy of SQL Server database. Before testing on staging environment we make copy of live database so testers have latest data. At current database size we can afford it.
Azure DevOps task for Azure SQL database copy
I don’t want to keep old copies of staging database. These copies are not needed and I can save few bucks when keeping Azure SQL Server clean. This is why I delete old version of staging database before making copy.
Here’s the Azure PowerShell task in release pipeline. It first deletes staging database and then makes copy of production database with same name.
Here’s the PowerShell script in text format for those who want to take it with copy and paste.
Remove-AzureRmSqlDatabase -ResourceGroupName "RgName" `
-ServerName 'dbserver' `
-DatabaseName 'StagingDb'
New-AzureRmSqlDatabaseCopy -ResourceGroupName "RgName" `
-ServerName 'dbserver' `
-DatabaseName "ProductionDb" `
-CopyResourceGroupName "RgName" `
-CopyServerName 'dbserver' `
-CopyDatabaseName "StagingDb"
This the command that works for me. Those who have to make more changes after copy can add additional commands after two shown above.
Wrapping up
It’s damn easy to automate build and release steps on Azure DevOps. First I thought it will be challenging and perhaps huge task to run. When I got it done it turned out to be way easier. Best of everything – it works pretty fast. After getting this task work it has run flawlessly this far.
Copy data from prod to staging ?! Totally direspectful from basic security rules and regulations. No sir, you copy to a temp database, you anonimize data, put data masking and so on before copying to staging.
Here, direct copy also means that you don’t have basic security like always encrypted in your prod database. Call me you DBA, I have some advices to give to him <:-)*
"before testing on staging environment we make copy of live database so testers have latest data"
that's not the way it should be done, not in 2020 and several big companies had data leakage because of that. The false believing that you cannot test without real data is a myth, you need to teach your testers or have a dataOps process to generate those data.
We will live with this solution as we don’t have such a sensitive data in our databases as you describe.
Hey, I know this is a pretty old post, but how would do this if your production and staging were in different subscriptions?
Hello Sir, why are you using an outdated version of PowerShell? instead of using the latest? Az PowerShell is now recommended instead of AzureRM PowerShell.
My scenario is we want to take a backup of the db before we deploy new changes and i am using Azure powershell task and inline script
New-AzureRmSqlDatabaseCopy -ResourceGroupName $rg -ServerName $servername -DatabaseName $db -CopyDatabaseName $copydbname
However get the below error any idea why and I am using the latest version of Azure Powershell
2021-06-07T19:28:36.7892925Z ##[error]The term ‘New-AzureRmSqlDatabaseCopy’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
2021-06-07T19:28:37.0193520Z ##[error]PowerShell exited with code ‘1’.