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.

The post Copy production database to staging on Azure DevOps appeared first on Gunnar Peipman - Programming Blog.