Strategies for version controlling database changes

Posts   
 
    
mattsmith321 avatar
Posts: 146
Joined: 04-Oct-2004
# Posted on: 28-Sep-2007 20:02:42   

Hi everyone!

I am familar with the fact that Visual Studio has Database projects that allow you to manage database changes.

I've used the processes outlined here in the past with success: http://www.ssw.com.au/SSW/Standards/Rules/DataSchemaStandard.aspx. This approach uses the "Create Scripts" and "Change Scripts" folders that the Database project creates for you. So you end up creating a new file anytime there are database changes. This file also has a bit of code that updates a table which tracks the changes that have been applied. It's a pretty neat approach for being able to tell what has been applied to a database and what state it is in. Almost kind of like a release number.

And I've also this 4Guys article (http://aspnet.4guysfromrolla.com/articles/071305-1.aspx). In it Scott recommends using separate folders for the different database objects (tables, stored procedures, etc.) and then version controlling the specific objects. It seems to me that this approach actually ties in better with a source control (a stored procedure has a history).

While the approach in the SSW article is great, I think it is somewhat less necessary with tools like SQL Compare and is the reason why I think that the 4Guys article might be better now.

Can anyone point me to any articles or books (we have books24x7) outlining how databases are managed and tied in with TFS? Given that the articles above are several years old, I would think that the strategies have probably been clarified in TFS.

Thanks!

mattsmith321 avatar
Posts: 146
Joined: 04-Oct-2004
# Posted on: 01-Oct-2007 03:03:20   

I just ran across this same question here: http://discuss.joelonsoftware.com/default.asp?dotnet.12.540097.11

gabrielk avatar
gabrielk
User
Posts: 231
Joined: 01-Feb-2005
# Posted on: 08-Oct-2007 23:15:26   

FYI also another thread on this forum:

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9982

Cheers, Gab

PilotBob
User
Posts: 105
Joined: 29-Jul-2005
# Posted on: 11-Oct-2007 17:15:32   

Not sure but if you own the full team suite you can get the new "Data Dude" SKU which is more designed to Db schema change management. The database projects in VS Pro doesn't really support this well... it is more of a container for stuff.

But, that is a bit expensive. Take a look at Db Ghost which is designed for Db change management and works well.

I think www.dbghost.com will get you there.

BOb

pat
User
Posts: 215
Joined: 02-Mar-2006
# Posted on: 22-Nov-2007 10:07:06   

I am using this here http://www.codeproject.com/cs/database/ScriptDB4Svn.asp It scripts an MSSQL database to the file system. With one file per database object e.g. for each table, view, stored procedure etc. all nicely organized in sub folders.

It took me a bit to setup and incorporate the bug fixes mentioned on the site so I am happy to give you the modified source code if your are interested.

Patrick