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.

Copy SQL Server database on Azure

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.

Copy SQL Server database on Azure DevOps

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.

Serverless360 Logo

A portal focused on Operations and Support for Microsoft Azure Serverless services

FREE TRIAL

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.

    3 thoughts on “Copy production database to staging on Azure DevOps

    • January 10, 2020 at 11:42 pm
      Permalink

      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.

    • January 11, 2020 at 12:14 am
      Permalink

      We will live with this solution as we don’t have such a sensitive data in our databases as you describe.

    • October 7, 2020 at 11:11 pm
      Permalink

      Hey, I know this is a pretty old post, but how would do this if your production and staging were in different subscriptions?

    Leave a Reply

    Your email address will not be published. Required fields are marked *