Stored procedures are source code for your application. They have to be stored in real version control system (subversion, git, ...)

I want a multilayered defence against losing important stuff.

No, there's no other way to keep history of database code except if you script them yourself. This is where a versioning system comes handy. No db platform has a history of all its versions of procedures/triggers/etc. It would mean to implement a versioning system inside its own platform, and that is already a complex one.

This looks promising, although I have to implement it myself. stackoverflow.com/questions/10820024/…

@a_horse_with_no_name I like making things easy for people and to save them when they make mistakes, even if they are programmers.

again: this is exactly what version control system is for. If your code is worth anything, there is no other way.

@cja: what programmer would refuse a versioning system?

@Marian lots of them. From general long-term "they slow the development process down" to critical exceptions like "but we need to rush this fix to production right now."

@Marian also SQL Server certainly does let you automatically track DDL changes using DDL triggers. It's not a substitute for proper version control, unit testing, and all that, but it can certainly be useful to figure who did what and when, and to some degree enable you to roll back, even in the absence of source control.

@AaronBertrand: especially for a quick fix a proper version control (and schema script management) is mandatory. Otherwise it will simply end in chaos - and desperate questions for "database comparison tools" (including an URGENT in the subject)

@a_horse_with_no_name I agree with you. I just know that in reality a good percentage of shops don't see that picture. A DDL trigger lets the DBA keep a running trail of changes even if there isn't (yet) source control buy-in from the rest of the team.

@AaronBertrand: Yep, I forgot about the DDL triggers. I used them mainly as an audit help, not for a versioning substitute. You're right, that's the easiest way. But I would have thought a wise developer wants to be safe against his/her own mistakes. This is where a versioning system makes it easier to recover from brain farts (which I have alot!). I don't like TFS, but God, it's useful :-).

Yes, nobody is discounting the value of proper version control. I just know from first-hand experience that getting proper version control implemented is not as easy as everyone here seems to make it sound - especially for database code (and again, I agree that it is just as important, but many DB developers and even DBAs will argue that their database backups are their version control).

Implementing the DDL trigger below, on the other hand, is about a 2-minute operation, and will get you to at least some level of version control very quickly, without all the politics and process changes that will be required to do it right.

Getting git up and running is easy, and git is the very best VCS. There is nothing special about database code that makes it more difficult to store in VCS than any other source code or any other text files.

@AlexKuznetsov it's not about the code Alex, it's about the process.

@AaronBertrand There is nothing special about database development process either - it can and should be done using modern approaches. If we still develop databases as if it were 1995, we end up with the productivity and stress level as of 1995 too ...

@AlexKuznetsov I am well aware of that. Sadly 90% of the world is not. Anyway I've said multiple times on this page that version control is important, that auditing is not a substitute, etc. I just disagree that changing existing process is as easy as your comment implies. A lot more politics involved than just installing git.

Auditing is good. Version control is outside the scope of this question.

"I don't want users updating stored procedures to have also to maintain a repository of stored procedures. This is too much work and people won't do it." This is a bad, bad sign.

I'm new here and people don't want to spend extra time using source control for stored procedures. There's no reason why SQL Server and its tools can't make it a lot easier.

@AaronBertrand I did not realize you meant politics. I agree, that could be a much bigger problem.