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?

  • 2
    If you are storing the current version in source control, then you make changes, check that in, and deploy, don't you already have the previous version in source control? The current version is simply sys.sql_modules.definition so, yes, you can get it from code. But before you even start making changes you should establish initial control by placing all current procedures in source control. Apr 25, 2014 at 17:56
  • Yes, we do have them source controled. I'm thinking on a scenario where 2 devs work on the same stored proc during a sprint. Neither on of them knows which will finish first and the version that they have to provide as rollback might be behind already if the other developer deployed his changes. Let's say 5 min before Apr 25, 2014 at 18:06
  • 1
    I think you need to consider exclusive check-out. Why are two developers allowed to make independent changes to the same stored procedure at the same time? I don't think there is enough code in the world to effectively solve that problem. Anyway, if they both check in their changes, you should be able to switch to whichever one is deemed correct, by pulling that version from source control. I'm not sure what complication you're trying to introduce to this scenario, but I'm sure it's unnecessary. If you have changes in both that you need to keep, you need a human to merge. Period. Apr 25, 2014 at 18:08
  • Agree with @AaronBertrand. This smells of wrong solution to the wrong problem. Apr 25, 2014 at 19:07
  • I agree that human has to do the merge an all. I was thinking of getting rid of the recreation of rollback scripts if somebody deployed something right before me. Apr 25, 2014 at 22:18

2 Answers 2


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);


--create trigger to capture proc ddl events
create trigger trg_sp_version
on database
for create_procedure, alter_procedure, drop_procedure
insert into sp_verison
(event_type, obj_name, obj_type, sql_text, principal, log_date)
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)'),

  • 1
    You need to be using nvarchar for a lot of these - especially sql_text. But still, I'm not sure that this step should really be necessary - if the developer has checked in to source control before they deployed (doesn't matter if it's 5 minutes or 5 years before the other developer), there is already a record of this in source control. This could be used as a safety measure in case developers bypass source control, but really that should happen a maximum of one time. Apr 25, 2014 at 18:37
  • Agreed with nvarchar - I will make the changes. As for version controls being able to manage this - I think every version control system is different, some might take care of it without my custom method. Apr 25, 2014 at 18:46

This problem is highly dependent on your current process.

Firstly, it matters whether you are source controlling upgrade scripts or a state of the schema. A good exploration of the pros and cons to each are here, but the latter makes it far easier to branch and merge: http://thefutureofdeployment.com/how-to-version-a-database/

Secondly, it matters whether the database being developed against is shared, or whether people are able to work against individual, sand-boxed databases. The latter is often thought to be best, but is not always practical. An amusing but informative article on the subject by Troy Hunt: http://www.troyhunt.com/2011/02/unnecessary-evil-of-shared-development.html

In the shared database case, an audit DDL trigger like Binaya suggests is a good safety net.

With sand-boxed copies of the database you can avoid the occurence of these problems and there are tools, including our Red Gate SQL Source Control, that can help you keep these databases in sync with both source control and each other.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.