2

I want to deploy database changes to an SQL Server using an Azure DevOps release Pipeline. There is a task called SQL Server Database Deploy, I see the option to deploy using SQL scripts however everywhere I read it is explained that this is to deploy a database.

My question is that if I have a directory of SQL scripts in my repository under /SQLScripts/*.sql could I use the scripts contained in this folder to deploy changes to stored procedures without effecting the remainder of the database?

e.i. Using something like the below script to update stored procedures...

USE [TESTDB]
GO
/****** Object:  StoredProcedure [dbo].[TestSP]    Script Date: 01/01/2023 12:00:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER  PROCEDURE [dbo].[TestSP]

-- Some new comment

I haven't attempted to deploy anything using this task as I don't want to risk altering the database in unintended ways. I would expect the task to run the stored procedures in order and for it to only update the scripts that I am passing through the pipeline.

Improve this question

2 Answers 2

Reset to default
1

You can also deploy using PowerShell in Azure Pipelines YAML, you can use the Invoke-SqlCmd cmdlet to call your .sql script file.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You have two different ways to execute SQL scripts on your database using the SQL Server Database Deploy task while deploying a DACPAC file that was built from a VS SQL DB project.

One is Pre Deployment Script which run before the deployment take place and the other is Post Deployment Script that run after the deployment happens. You can also have multiple scripts for the same.

Go to the properties of the file and under the Build option, choose PreDeploy or PostDeploy depending on your requirement.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.