SQL Server – How to Rollback Stored Procedure with Generic Script

sql servert-sql

We're trying to switch to feature-driven development in my organization. We're planning on using a new branch on our source control for each feature and merge when development is complete. I think it will work great for application code since the rollback is to remove the merge check-in. For our db schema work might be difficult though.

Is there a way of retrieve the current version of a stored procedure, by code, at the time a new version is being deployed in order to create a rollback script automatically?

Best Answer

You can capture the event data with EVENTDATA() function and use a ddl trigger to persist the information into a version table, which can be used as rollback script, if needed. That way you will have a rollback script with timestamp stored in the database, no matter what happens within your version control. My sample implementation code is given below.

-- create version table to store procedure versioned text with timestamp
create table sp_verison(
event_type nvarchar(24),
obj_name nvarchar(256),
obj_type nvarchar(24),
sql_text nvarchar(MAX),
principal nvarchar(50),
log_date datetime);

go


--create trigger to capture proc ddl events
create trigger trg_sp_version
on database
for create_procedure, alter_procedure, drop_procedure
as
insert into sp_verison
(event_type, obj_name, obj_type, sql_text, principal, log_date)
values(
eventdata().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(24)'), 
eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(256)'), 
eventdata().value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(24)'), 
eventdata().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 
eventdata().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)'),
current_timestamp);

go
Related Question