Build a pipeline in AzureDevOps for Sql Server on prem to run sql scripts

Question

I need to build a pipe line in Azure DevOps to run sql scripts on premises, I know this it works because I've done it in the past (3 years ago but don't remember what task I've used). Please can someone help me with the right steps (which task/components) to use in worder to build it. Not looking to build a pipe line to deploy DACPAC files.

The best will be to come up with all the steps like: Create Artifact, Create pipeline (all steps and task), Create release (all steps and task).

Thank you

Answer 1

You can try with any of the ways below:

1. Use a PowerShell task to execute the Invoke-Sqlcmd cmdlet like as below.

   Invoke-Sqlcmd -InputFile "{scriptfilepath}" -ServerInstance {SqlServer} -Database {DbName} -Username "{userName}" -Password "{Password}" -QueryTimeout 36000 -Verbose
   

2. On the Marketplace of Azure DevOps, you can find some pipeline task extensions that can be used to run SQL scripts. You can install one of them into your Azure DevOps organization/collection for use.

   • Execute SQL Script
   • Run SQL / SQLCMD Scripts passing multiple SQLCMD variables