CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Kamyar Nazeri
Kamyar Nazeri

Reputation: 26474

SQL Server stored procedure restores to previous one

There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure changes to the one before Alter! This has happened several times during past hours!

I am certain that nowhere inside the program or manually I change the Stored Procedure. I checked all Agent Jobs, There's only one Maintenance job for hourly backup, and another one for Replication, however Replication Schema Changes is set to false, and Stored Procedures are not defined in the list of Publication Articles

Is there any other possible reason for this behavior? Do I need to manually set a CHECKPOINT in the SQL Server database?

Upvotes: 1

Views: 4606

Answers (1)

John Dewey
John Dewey

Reputation: 7093

You could create a DDL trigger to log ALTER_PROCEDURE events like this:

CREATE TABLE dbo.AlterLog(EventType VARCHAR(50)
, ObjectName VARCHAR(256)
, ObjectType VARCHAR(25)
, TSQLCommand VARCHAR(MAX)
, LoginName VARCHAR(256)
);
GO

CREATE TRIGGER AlterProcs
ON DATABASE
FOR ALTER_PROCEDURE
AS

SET NOCOUNT ON; 

DECLARE @data XML
SET @data = EVENTDATA()

INSERT INTO dbo.AlterLog(EventType, ObjectName, ObjectType, TSQLCommand, LoginName)
VALUES(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'), 
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)')
)
GO

Upvotes: 10

Related Questions