How to backup database in Azure devops pipeline

A devops story of taking a backup before you release to production.

Published 16th January 2019
Versions: OnPrem MS SQL Server 2005-2016

This blog post is part of a series of posts “A devops story – from Azure to on premise CI/CD” which will be published in future posts.

NOTE! this is one way of doing backup in a devops context, it is not an Azure official best practice. This post is meant as an inspiration for your implementation.

Even if this example is based on an “on premise” implementation, it could be implemented in a cloud scenario.

Scenario: In a devops pipeline, before releasing your artifact/code to Staging and Production, you want to take a database backup.

Prerequisites:

  • You have a build in Azure Devops or Github
  • You have a Release with x tasks
  • You have a windows agent for on prem commands

Main Concept

Before deploy, request an url on site (/DatabaseBackupBeforeRelease), that triggers a SQL stored proc in database, that in turn takes an instant backup file (.bak)

How?

  1. Use command line task to trigger a power shell script
  2. Power shell script to request an url
  3. Http trigger that trigger the stored procedure

Azure Devops Release Tasks

I’m using a the command line task to trigger the Power Shell script:

*Important* use Version 1

Power Shell script

We are using a local PS script to request the database backup to the application. We do run this script localy because we wanted to secure the url by IP restrictions. It would be posible to run the PS script inline in an PS task in Azure devops.

if ($args[0] -eq "true"){

    Write-Host  "Taking a database backup "

    $req = Invoke-WebRequest $args[1] -UseBasicParsing -TimeoutSec 240  #arg1=true arg2=https://www.site.com/DatabaseBackupBeforeRelease?version=20180913.1&securetoken=x

       if ($req.statuscode -ne 200) {
            Write-Host "Failed to request $uri"
            #Write-Host ($req | out-string)
	        exit 1
         }
         else {    	

    	    $ver = $req.Content


		    If ($ver -eq "1") {
			    Write-Host "backup is taken, return value: " $ver
		    }
		    Else {
		    	    Write-Host "Error, no backup taken, return value: " $ver
 			    exit 1
		    }

  		    exit 0
        }
}
else {
    Write-Host  "No database backup needed - value set to: " $args[0]
}

 

C# MVC code

API call example:

[HttpGet]
public JsonResult DatabaseBackupBeforeRelease(string version, string securetoken)
{
    int count = 0;
    if (securetoken!="yourimplementation")
        return Json(count, JsonRequestBehavior.AllowGet);


    System.Data.SqlClient.SqlConnectionStringBuilder connBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
    connBuilder.ConnectionString = ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString;
    string databaseBackupPath = ConfigurationManager.AppSettings["DatabaseBackupPath"] + "";
    if (!string.IsNullOrEmpty(databaseBackupPath))
    {
        _databaseExecutor.Execute(() =>
        {
            count = Convert.ToInt32(_databaseExecutor.GetScalar("backupdatabase",
                CommandType.StoredProcedure, connBuilder.InitialCatalog, databaseBackupPath, Sanitizer.GetSafeHtmlFragment(version)));
        });
    }

    return Json(count, JsonRequestBehavior.AllowGet);
}

IMPORTANT: SECURING the MVC Request

I secured the the path /DatabaseBackupBeforeRelease by adding a folder “DatabaseBackupBeforeRelease”, and in “IIS/IP Address and Domain Restrictions” adding an IP restriction.

SQL Stored Procedure

Install this stored procedure in your database, which executes the backup with SQL.

The SQL procedure is taking three parameters:

  1. databaseName,
  2. dir to save the backup,
  3. Version > to version the database
create procedure dbo.backupDatabase
  @databaseName varchar(100),
	@backupDirectory varchar(1000),
  @releaseid varchar(100)
as
declare @backupFileName varchar(100),
	@databaseDataFilename varchar(100), @databaseLogFilename varchar(100),
	@databaseDataFile varchar(100), @databaseLogFile varchar(100),
	@execSql varchar(1000)
-- If the backup directory does not end with ''\'', append one
if charindex('\', reverse(@backupDirectory)) > 1
	set @backupDirectory = @backupDirectory + '\'

-- Create the backup file name based on the backup directory, the database name and today''s date
set @backupFileName = @backupDirectory + @databaseName + '-backup-before-release-' + @releaseid + '-date-' + format(getdate(),'yyyy-MM-dd-HH-mm-ss') + '.bak'

set @execSql = 
'backup database ' + @databaseName + '
to disk = ''' + @backupFileName + '''
with
  copy_only,
  noformat,
  noinit,
  name = ''' + @databaseName + ' backup '',
  norewind,
  nounload,
  skip;select 1'

exec(@execSql)
SEO Terms
  • Automatic take database backup on release
  • backup database to disk example

About the author

Luc Gosso
– Independent Senior Web Developer
working with Azure and Episerver

Twitter: @LucGosso
LinkedIn: linkedin.com/in/luc-gosso/
Github: github.com/lucgosso

9 thoughts on “How to backup database in Azure devops pipeline”

      1. I saw that now 🙂

        I have a similar thing, but it all happens inside SQL Server as a job where I take a bak-file and restore it and update users and update site settings and all.

        I also download all VPP-files from Azure with AzCopy and I do this every night so every day I have an environment that looks like yesterday production.

        One thing I see in your SQL script is that you are missing av very important thing since it is a onPrem database and that is to take a Copy Only database backup.

        If you do not take CopyOnly then you will mess with the maintenance job for backups.
        Read more about it here:
        https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server?view=sql-server-2017

        1. Out of simplicity, to run SQL module i would need to install an agent on the server running SQL Server too, and the SQL server is not open to internet. With this example I only need an agent on the webserver. It is a matter of taste or practical options.

Leave a Reply to Luc Gosso Cancel reply

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