CODEWITHSUNDEEP
sql-serverazure-devopsazure-pipelinessql-server-data-tools
spottedmahn
spottedmahn

Reputation: 16031

backup database prior to deployment in pipeline

Is there a first-class citizen* way to backup the DB before deploying the dacpac in a Azure DevOps pipeline (yaml not classic gui)?

If there isn't an "easy button", how do I do this?

Reference: WinRM SQL Server DB Deployment task

*When I saw first-class citizen, I mean not rolling my own custom solution but using a generic solution provided by the MSFT eco-system.

Upvotes: 2

Views: 2675

Answers (3)

Mike Gledhill
Mike Gledhill

Reputation: 29213

Here's how I did it, using an inline PowerShell script, in my Azure Pipeline:

Write-Host "Backing up database: $(DatabaseName) on $(DatabaseServer)"
Write-Host ""

# Backup to a filename like "MikesDatabase_20231201.bak"
$sqlBackup ="BACKUP DATABASE [$(DatabaseName)] TO DISK = '$(BackupDbLocation)\$(DatabaseName)_ymd.bak' WITH COMPRESSION, INIT, SKIP, STATS=10 "
$sqlBackup = $sqlBackup.replace("ymd","$(get-date -f yyyyMMdd)");

Write-Host "Running Invoke-Sqlcmd backup command:"
Write-Host $sqlBackup
Invoke-Sqlcmd -ServerInstance "$(DatabaseServer)" -Database "$(DatabaseName)" -Query "$sqlBackup"  -Verbose
Write-Host ""

In my Azure Library, I have three variables defined:

  • DatabaseServer
  • DatabaseName
  • BackupDbLocation

... so I can modify these, per environment.

Upvotes: 1

bbsimonbb
bbsimonbb

Reputation: 29020

How are you creating your dacpac? There is a sqlpackage option to backup database before deployment, and there are numerous ways of specifying it. You can specify it on the command line, with /p: "BackupDatabaseBeforeChanges=True", or you can specify it in your publish profile in your database project. Right-click the project => publish => Advanced => "Backup database before deployment", then save the profile in the project.

Backup database before deployment

If you're using the using the 'SQL Server database deploy' task in Azure, you can put /p: "BackupDatabaseBeforeChanges=True" in the additional arguments box. SQL Server database deploy on Azure

Upvotes: 2

spottedmahn
spottedmahn

Reputation: 16031

Source control side

  1. add a sql script to the DB project, backup.sql
  2. set the build action to none

backup script

EXECUTE dbo.DatabaseBackup @Databases = '__DbName__'
    ,@Directory = '__DbBackupLocation__'

Build side

  1. add that file to the ADO artifact
steps:
- publish: $(System.DefaultWorkingDirectory)/db-backup.sql
  artifact: DB Artifact

reference: Publish artifacts

Library side

  1. in a variable group, add variables for DbName & DbBackupLocation

reference: Create a variable group

Deployment side

assumption: you're using a deployment task so the artifact(s) are automatically downloaded; reference: Artifacts in release and deployment jobs

  1. import the variable group; reference: Use a variable group
  2. call the replace token task from qetza/vsts-replacetokens-task
steps:
- task: replacetokens@3
  displayName: DB Untoken
  inputs:
    rootDirectory: $(Pipeline.Workspace)\DB Artifact\
    targetFiles: *.sql
    tokenPrefix: __
    tokenSuffix: __
  1. use SqlDacpacDeploymentOnMachineGroup to execute backup.sql
steps:
- task: SqlDacpacDeploymentOnMachineGroup@0
  displayName: DB Backup
  inputs:
    taskType: sqlQuery
    sqlFile: $(Pipeline.Workspace)\DB Artifact\db-backup.sql
    serverName: localhost
    databaseName: master
    authScheme: windowsAuthentication
    additionalArgumentsSql: -Verbose -Querytimeout 0

Upvotes: 0

Related Questions