Adding a migration after a manual change to production

asadc

Hello,

Could someone please advise on how to correctly sync the database project after a stored procedure was created manually in production? The project is currently configured to generate scripts for database objects such as stored procedures.

Thanks.

Sergio R

Hi,

Are you using a SQL Source Control or a SQL Change Automation project?

Kind Regards,

asadc

It's a SQL Change Automation project.

Sergio R

Assuming that you are using the default option of using Programmable Objects, then it's just a case of adding the Stored Procedure to your development database and importing it.
This will generate a Programmable Objects script that will be deployed in Production and will drop the existing Stored Procedure and recreate it.

asadc

We're not using programmable objects, so the script that's generated will be a CREATE. I ended up manually modifying the generated script by adding a DROP, I'm not sure if there's a better way. =

Sergio R

We would advise using the same approach used in Programmable Objects, drop the the object if exists and then script the CREATE:

For example:

IF OBJECT_ID('[HumanResources].[uspUpdateEmployeeHireInfo]') IS NOT NULL

DROP PROCEDURE [HumanResources].[uspUpdateEmployeeHireInfo];