How to merge changes from a source-controlled VS2010 database project to local development DB

We're trying out VS2010 database projects for a new development, using the following dev cycle:

• Use Management Studio to develop changes on a local DB instance (using the designers etc)
• Use VS2010 schema compare to sync / import these changes to the VSDB project
• Check in the VSDB project and run automated build / test etc

When I want to 'get latest' from source control, I then:

• Update the VSDB project files from source control
• Use Schema Compare to push the changes from the project to my local database instance

This is where it starts to break down... Because schema compare is trying to synchronise the two versions, it attempts to undo any changes I've made to my local database as part of my own feature development.

Obviously, you can tell schema compare to skip changes to the objects I've modified, but sadly this doesn't always work correctly: http://connect.microsoft.com/VisualStudio/feedback/details/564026/strange-schema-compare-behavior-sql-2008-database-projects.

Fundamentally, the problem exists because the definitions in the VSDB project are not automatically synchronised with my local database; thus I need to use Schema Compare to do a 'poor mans merge' every time I get a change.

One possible solutio开发者_开发百科n could be to:

• Use Schema Compare to sync any changes from my local DB to the VSDB project first
• Update the VSDB project from source control (therefore using the source control tooling to do the merge, rather than Schema Compare)
• Schema Compare the changes from source control into my local DB instance

...which is far from ideal.

Is RedGate SQL Source Control better in this regard?

What about the new 'Juneau' SQL toolset?

you use 'Deploy' to push source changes to the database. Either Deploy Solution from the top-row Build menu, or you can right-click on the project in the Solution Explorer and select Deploy.

Deploy is configurable in the Project properties.

HTH

Your process is backwards which is why this is difficult. Changes should flow from VSDB to your database, not the other way around. Try this:

1. Use the designers in Management Studio if you like them but script out any changes you make and add them into your VSDB project.
2. Instead of using Schema Compare use the built in Deployment functionality. This will automatically script and deploy the incremental changes to your local database in a single click

Since you mention other possible solutions, I'll elaborate on how our shop manages data structure changes and propogation to dev db's.

For tracking and applying differences, we've written a C# app that effectively abstracts database actions out to classes that we append to an Action list. The engine dynamically loads modules that represent database versions, and adds each item in the module to a list of actions to be performed for that version upgrade, then processes the list. Actions include DataRowInsertAction, TableCreateAction, ColumnModifyAction, etc.

One benefit of using this approach was that we were able to commit standard .cs files to subversion and users can bring their own dev databases up-to-date simply by checking out the latest and runnig it. Another huge advantage is that we can target multiple database engines, since the Actions themselves know what SQL to render based on which database engine is being targeted.

As a side note, we use AdeptSQL to compare databases, and love it. It'll create a complete list of differences, and you can generate a script to go either direction (given Database 'A' and Database 'B', upgrade A to B, or downgrade B to A.)

For a small additional charge, they offer extended functionality to perform a data diff as well.

http://www.adeptsql.com/

The idea behind SQL Source Control is basically to turn the development process on its' head - instead of working with database scripts and pushing the changes to a database, you make the changes to the database and SQL Source Control calculates the deltas and updates the local scripts and allows you to commit the changes to your source control system.

SQL Source Control currently only integrates with SQL Server Management Studio, but there is now a VS package called SQL Connect that you can use in VS 2010 to work in much the same way as in SQL Source Control. http://www.red-gate.com/products/sql-development/sql-connect/index-2