Best way to version control T-SQL?

Question

Possible Duplicate:
Stored procedures/DB schema in source control

What's the best way to version control my tables, views, sprocs, etc? Preferably automated or at least semi-automated :)

Thanks

Answer 1

I asked this one yesterday and got some nice responses:

Stored procedures/DB schema in source control

Answer 2

The articles from K Scott Allen say it all: http://odetocode.com/Blogs/scott/archive/2008/01/31/11710.aspx

Answer 3

Write migration scripts for all db changes and keep them in a repository. Enforce a policy of making all changes to the db only by running a script; that way there is a record of what has been done, and a way to revert it. Investigate whether there's a migrations framework available for your favorite language/db combination.

Answer 4

I use Visual Studio 2008 Pro create Database projects (Other project types -> Database). We already use SVN as a code repository, so a project with a bunch of .sql files representing your stored procedures is just another thing to put in the repository - you can see diffs/history etc. This works the same with VSS or any other repository you use.

The nice thing about Database projects is that your project will remember your connection string, and all you have to do is right click on a .sql file (or select all of them at once!) and select run to update it in the db. This makes it easy to update your .sql files from the repository and run them all to update all your stored procedures, verifying your database is updated in seconds.

You can also select create a LINQ project (Visual C# -> Database) and store all your LINQ code in your repository.

Hope that helps!

Answer 5

I'm using Visual Studio Database edition which can export the schema from SQL Server in to a Visual Studio project. This is then stored in Source Control and can be deployed where ever needed. The VS Database project is just a bunch of scripts though and it's a clunky way of working.

A more robust method would be to use a database migration framework and if you're working with .Net check out this blog post for a good description http://flux88.com/NETDatabaseMigrationToolRoundup.aspx.

Update

As mentioned in the comments, this page is no more. So here is the last known snapshot from wayback machine http://web.archive.org/web/20080828232742/http://flux88.com/NETDatabaseMigrationToolRoundup.aspx

Answer 6

Try Randolph, One of the best SQL Version control tools I know.

Answer 7

I have written a DDL trigger which logs all the changes done to the definition of SQL objects (triggers, tables, SP, view etc). I could very well invoke extended SP from the trigger and store the details in another Database and use that as repository. But if your team is really disciplined any source control should do the trick. The trigger is used as an audit mechanism and it's ideal for teams which are geographically scattered.

Answer 8

If you were super lazy you could use the SMO (SQL Server Management Objects) or if using SQL Server prior to 2005 the DMO (distributed managmeent objects) to script out all tables/views/stored procedures daily and then compare the script to the script in source control and if there are any changes check the new version in. You won't be able to necessarily have as pretty of a script as if you just created all db changes in scripts, but at least you can recreate all tables/stored procedures/views. For example, in my table creation scripts there are often comments.

Here is an article to get you started on scripting: http://www.sqlteam.com/article/scripting-database-objects-using-smo-updated.

Again, this is mainly if you are too lazy to bother with version control and it won't help if you change something twice in one day. Also any data migration scripts still have to be saved and checked in because this won't pick up ad hoc SQL, only database objects.