20

I have SQL script which I want to execute using azure DevOps pipeline

I found multiple tasks for SQL but can not find any required task where I can pass sql server , database name and login details. Is there any task available for this ?

If there is not any task available and only way to execute is powershell script any sample available script for this ?

Krzysztof Madej
  • 32,704
  • 10
  • 78
  • 107
megha
  • 621
  • 2
  • 11
  • 36

3 Answers3

16

You can use PowerShell to execute sql scripts. Example:

Invoke-Sqlcmd -InputFile "$(scriptfilepath)" -ServerInstance $(sqlserver) -Database $(dbname) -Username "$(username)" -Password "$(pwd)" -QueryTimeout 36000 -Verbose

Add custom variables (scriptfilepath, sqlserver, ...) and set values to them.

  1. PowerShell task
  2. Define variables
  3. Invoke-Sqlcmd
Shamrai Aleksander
  • 13,096
  • 3
  • 24
  • 31
6

You can use Invoke-Sql command like this

$SQLServer = "TestServerOne"
$db3 = "TestDB3"
$qcd = "PRINT 'This is output'"
Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd -Username "User" -Password "Password" -Verbose

Make sure SqlServer module is installed. It works also with Powershell Core

You can also try to use Run SQL Server Scripts Task extension

Krzysztof Madej
  • 32,704
  • 10
  • 78
  • 107
3

If you want to do this in Azure Release Pipeline (classic), you can use the ' Azure SQL Database deployment ' block which uses Invoke-Sqlcmd under the hood.

enter image description here

With that, you can configure it to execute an SQL script on a given database under one or your subscriptions or service connections.

enter image description here

Ε Г И І И О
  • 11,199
  • 1
  • 48
  • 63
  • how can we run multiple .sql files in one task? – Schatak Dec 13 '22 at 17:22
  • @Schatak I don't know a proper way but if there's only a few I'll just duplicate the task. If you have so many of them, I'd say you do a file merge to create a single file during your build steps. Any text file merging technique would be fine for SQL too, but I haven't done this on a pipeline, so I don't really know what is available. – Ε Г И І И О Dec 13 '22 at 17:30
  • Does this also works if the SQL servicer is in private vnet in other subscription and no public endpoint? – ExploringApple Apr 17 '23 at 12:42