How to keep Stored Procedures and other scripts in SVN/Other repository?

Question

Can anyone provide some real examples as to how best to keep script files for views, stored procedures and functions in a SVN (or other) repository.

Obviously one solution is to have the script files for all the different components in a directory or more somewhere and simply using TortoiseSVN or the like to keep them in SVN, Then whenever a change is to be made I load the script up in Management Studio etc. I don't really want this.

What I'd really prefer is some kind of batch script that I can run periodically (nightly?) that would export all the stored procedures / views etc that had changed in a given timeframe and then commit them to SVN.

Ideas?

Answer 1

I find it best to treat Stored Procedures just like any other compilable code: Code lives in the repository, you check it out to make changes and load it in your development tool to compile or deploy the code.

Answer 2

Sounds like you're not wanting to use Revision Control properly, to me.

Obviously one solution is to have the script files for all the different components in a directory or more somewhere and simply using TortoiseSVN or the like to keep them in SVN

This is what should be done. You would have your local copy you are working on (Developing new, Tweaking old, etc) and as single components/procedures/etc get finished, you would commit them individually until you have to start the process over.

Committing half-done code just because it's been 'X' time since it was last committed is sloppy and guaranteed to cause anyone else using the repository grief.

Answer 3

Not sure on your price range, however DB Ghost could be an option for you.

I don't work for this company (or own the product) but in my researching of the same issue, this product looked quite promising.

Answer 4

I'd recommend Redgate SQL Compare for this - it allows you to compare database versions and generate change scripts - it's also fairly easily scriptable.

Answer 5

You can create a batch file and schedule it:

• delete the contents of your scripts directory
• using something like ExportSQLScript to export all objects to script/scripts
• svn commit

Please note: That although you'll have the objects under source control, you'll not have the data or it's progression (is that a renamed field, or 1 new field and 1 deleted?).

This approach is fine for maintaining change history. But, of course, you should never be automatically committing to the "production build" (unless you like broken builds).

Although you didn't ask for it: This approach also won't produce a set of scripts that will upgrade a current DB. You'll only have initial creation scripts. Recording data progression and creation upgrade scripts is beyond basic source control systems.

Answer 6

I should've been a little more descriptive. The database in question is for an internal ERP system and thus we don't have many versions of our database, just Production/Testing/Development. When we've done a change request, some new fancy feature or something, we simply execute a script or series of scripts to update the procedures in question on the Testing database, if that is all good, then we do the same to Production.

So I'm not really after a full schema script per se, just something that can keep track of the various edits to the stored procedures over time. For example, PROCESS_INVOICE does stuff. It gets updated in some minor way in March. Some time later in say May it is discovered that in a rare case customers get double invoiced (or some other crazy corner case). I'd like to be able to see what has happened over time to this procedure. Currently the way the development environment is setup here I don't have that, which I'm trying to change.

Answer 7

You can always try integrating SourceSafe with SQL Server. Here's a quick start : link . To work with it you've got to have Managment Studio Developers Edition.

Answer 8

I can recommend DBPro which is part of Visual Studio Team Edition. Have been using it for a few months for storing all parts of the database in Team Foundation Server as well as for deployment and database compares, etc.

Of course, as someone else mentioned, it does depend on your environment and price range.

Answer 9

I wrote a utility for dumping all of the relevant parts of my db into a directory structure that I use SVN on. I never got around to trying to incorporate it into the Manager but, if you're interested, it's here: http://www.reluctantdba.com/dbas-and-programmers/sqltools/svnforsql2005.aspx

It's free and, since I regularly run it, you know any bugs get fixed quickly.

Answer 10

Based on your expanded question, you really want to use DDL triggers. Check out this article that details how to create a changelog system for your database.

Answer 11

Hi, I recently write a small article that explain how to do this:

http://www.codeproject.com/KB/database/SQLScripter.aspx

Cheers, Gabriele