rolling back datbabase changes with sqlcmd utility

Question

I have a release pipeline which applies database changes with 'SqlCmd.exe'. I am trying to execute a stored procedure using this command-line utility:

/opt/mssql-tools/bin/sqlcmd -S tcp:$(Server) -d $(Database) -U $(UserName) -P '$(Password)' -b -i "$(ScriptFile)"

Once something goes wrong in the script file, I want to SQLCMD.EXE automatically rollback all the changes.

I should mention that there is no TRANSACTIONS management inside the script file.

Please help me to learn how to resolve this.

Answer 1

You probably have to add rollback transactions in your script file. There is not configurations in azure release pipeline to control the rollback behavior. See example here to add transactions in scripts.

If you donot want to add transactions in the script file. You can try adding a poweshell task in release pipeline run below script to append a BEGIN TRANSACTION and END TRANSACTION to your query contents.

$fullbatch = @()
$fullbatch += "BEGIN TRANSACTION;"

$fullbatch += Get-Content $(ScriptFile)
 
$fullbatch += "COMMIT TRANSACTION;"

sqlcmd -S tcp:$(Server) -d $(Database) -U $(UserName) -P '$(Password)' -b -Q "$fullbatch"

See example in this thread.