3

How do you guys maintain your stored procedures? I'd like to keep versions of them for a few different reasons.

I also will be setting up cruisecontrol.net and nant this weekend to automate builds.

I was thinking about coding something that would generate the create scripts for all tables/sprocs/udf/xml schemas in my development database. Then it would take those scripts and update them in source control every couple hours.... Ideally, I'd like to make this some sort of plugin/module for cruisecontrol.net.

Any other ideas?

Jon Seigel
  • 12,251
  • 8
  • 58
  • 92
dub
  • 33
  • 2

2 Answers2

3

We use Red Gate's SQL Toolbelt at work for this very purpose. Works like a charm.

http://www.red-gate.com/

Fredrik E
  • 1,840
  • 13
  • 18
  • I use Red Gate sql compare and sql data compare to deploy changes from one environment to the other. And yes, this is (imo) by far the best tool for this. But they have a component that versions database create scripts? – dub Jan 08 '10 at 00:13
  • Yes, and there are several different ways to do it too. One can generate scripts that are automatically checked in from SQL Compare or use SQL ChangeSet (we use the latter approach). They are releasing another piece of software as a stand-alone application with Management Studio integration too soon. http://www.red-gate.com/Products/SQL_Source_Control/index.htm Knowing Red-Gate's other products I'm sure it will be good too. – Fredrik E Jan 08 '10 at 01:06
3

Every object, stored procedure or otherwise, and schema change is handled by a script (text file) under subversion control. So just like any other file in your project. Changes to object/schema are made by executing those scripts against the relevant database. Our build process aggregates those into one big script for convenience. And schema change scripts are written in a way so that they can be rerun without warnings/errors ( if not exists... exec...)

I would advise against editing table definitions or creating objects directly in the database using query tools, then after the fact trying to extract those changes into scripts.

Instead make the changes to your dev database the same way as you will in qa/production, using those version-controlled scripts.

MikeW
  • 5,702
  • 1
  • 35
  • 43