Even if you do not have direct network access to your live database, the principals discussed in this post can be easily accomplished by taking a backup of your production database and restoring it locally.
Version Test1 will have a few more fields, as we’ll treat this like our in-house development database, and Version Test2 will have less fields, as this will be our production/staging database that we want to bring inline with our development database.
You may think i have used a really simple example, and i would tend to agree that my lack of creativity in the use of this example is pretty crap – in my defence though most database changes will be iterative. In the newly opened window create a new database connection to your source database (the database we want to compare from) and your destination database (the database we want to compare and merge the schema from the source to).
This shows us a more fine-grained display of the changes between the tables, and allows us to Skip any of the changes individually.
One of the most common things that you usually want to skip is the change of filename for the database and log file, noted by the image below showing that the tool wants to drop the file and create a new one. When we’ve had a bit of a play with the above to get a good outcome for our schema comparison/schema update, we can then shift to seeing a script that can be run against our destination database (in this instance the database Version Test2) to bring it in synch, by simply clicking on the icon in the toolbar that shows the label Show Schema Update Script.
For the purposes of this how-to I'll create two sample databases to compare between so that we have an example to run with.
We will call these two databases Version Test1 and Version Test2.
But first a little caveat; If you have worked with SQL databases in the production of your application, you have probably heard of the developer tool producer Red Gate.
They produce a whole bunch of tools to make a windows developer’s life easy – in the case of this post specifically, they make a tool called SQL Compare that is pretty popular among the community.
The Export to editor button in the toolbar will take the schema script and open it in a new SQL management studio query window, and the Export to file…
does exactly as it says and brings up a Save File dialog.
This post is part of a multi-part series on Visual Studio’s Database tooling.