Best way to version control T-SQL?

Question

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

Thanks

Answer 1

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 2

I asked this one yesterday and got some nice responses:

http://stackoverflow.com/questions/77172/stored-proceduresdb-schema-in-source-control

Answer 3

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 4

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

Answer 5

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.

Answer 6

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.