Continuous Deployment execute database scripts with TFS

Posted by

In another post, we’ve learned how we can use a Build definition and Release Management in a CI/CD pipeline. Please go through the articles if you need more information on these topics.

In this post, I’ll explain how we can use Release Definition to execute database scripts as part of the continuous deployment strategy. We will add a task to execute PowerShell script to achieve this.

Create Release definition

The task to be added is PowerShell under Utility. This task should be added before the Application files are deployed in the CD pipeline.

Create a Release definition for your Release pipeline as shown below, the path for the PowerShell script should be a shared path and the database Server is accessible.

Add the below code snippet to the PowerShell script. The script should be modified as per the requirement.

$localScriptRoot = "C:\Scripts"
$Server = "dbserver"
$scripts = Get-ChildItem $localScriptRoot | Where-Object {$_.Extension -eq ".sql"} | Sort-Object -Property Name
$qt = 0
foreach ($s in $scripts)
        Write-Host "Running Script : " $s.Name -BackgroundColor DarkGreen -ForegroundColor White
        $script = $s.FullName	
        Invoke-Sqlcmd -ServerInstance $Server -InputFile $script -username "TFSBuildUser" -password "****" -querytimeout $qt
Write-Verbose "scripts executed successfully" -verbose

The scripts to be executed are present at the location C:\Scripts with extension “.sql”. The password can be also passed as an argument to the PowerShell script and added to the Release Definition secret variable.

Use the Triggers tab to link to the required Build definition of your Project so that the Release definition is integrated with the Build pipeline. The linking can also be done using the Artifacts tab.

You can setup multiple environments using this strategy like Dev, POC and Production.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.