none
Comparing two versions of a database schema

    Question

  • I'm using Visual Studio Community 2015 (v14.0.25123.00 Update 2) and Microsoft SQL Server 2008 R2. I also recently upgraded my computer to Windows 10.

    I'm trying to compare the databases of two different versions of the MSSQL database that my web application (Moodle) uses. I've registered the db server and connected to both versions of the database. I have installed both versions of the application and they use localhost copies of the database on my computer.

    I've managed to compare the two versions of the database in Visual Studio and it shows me the CREATE TABLE statements for each table in the two databases. But I can't work out how to create the ALTER TABLE statements that contain the changes between the two databases.

    https://social.msdn.microsoft.com/Forums/en-US/48f58083-65ed-404e-b0fa-1323f34ff925/sql-schema-compare-configure-to-alter-not-dropcreate?forum=vstsdb describes the issue that I'm having and it suggests doing the following:

    "...if these two databases do not contain any data, if you would like to generate the ALTER script of your schema compare, you can acheive that by exporting the scripts to the editor or file. Go to Data menu->Schema Compare->Export To->Editor|File. With this option, you will get the change script."

    But I cannot find the "Data menu" in Visual Studio Community 2015. Is that 'Export To' option not available in my version of Visual Studio?

    I'm only interested in generating a "diff file" containing the database scheme differences. The databases are actually empty, so I'm not interested in comparing the DATA. Can I do this in my version of Visual Studio?

    Thanks.

    Tuesday, May 10, 2016 2:52 PM

Answers

  • Hi luisdev,

    After searching and testing in Visual Studio 2015, I don't find such functions to implement your requirement. I'm afraid that current version of Visual Studio 2015 is not support your requirement, which is default design.

    I suggest you submit your requirement to Visual Studio User Voice and then share the feedback link here. It can let other communities who has the same requirement to vote and add comments.

    Before that, I recommend you use previous version of Visual Studio, such as VS2010.

    Best Regards,
    Weiwei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 12, 2016 1:28 AM
    Moderator

All replies

  • Hi luisdev,

    As far as I know, the Date menu -> Schema Compare -> Export To -> Editor|File function is belong to previous version of Visual Studio.

    In Visual Studio 2015, this function can operated from Tools menu. Please try with following steps to implement your requirement.

    1. Open Tools menu to choose SQL Server -> New Schema Comparison option.
    2. Select Source and Target on the top of compare windows.
    3. Then click the Compare button to start compare these two database.
    4. After generate the compare result, please click the Generate Script button to generate compare result script.
    5. The ALTER TABLE statements will stored in this script file. And you also can click the Update button to update your target database directly.

    Best Regards,
    Weiwei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 11, 2016 2:49 AM
    Moderator
  • Thanks Weiwei,

    Yes I found that option, but it still uses some temp tables when new columns are being added to existing tables. I want it to generate ALTER TABLE statements instead of using temp tables to add the the new columns.

    I'm doing this for documentation and change control purposes. I'm not going to be using the ALTER TABLE scripts to make the changes on the database - the application installer does all that.

    So, is there a way to get Visual Studio to generate ALTER TABLE statements instead of using temp tables?


    • Edited by luisdev Wednesday, May 11, 2016 6:35 AM
    Wednesday, May 11, 2016 6:35 AM
  • Hi luisdev,

    After searching and testing in Visual Studio 2015, I don't find such functions to implement your requirement. I'm afraid that current version of Visual Studio 2015 is not support your requirement, which is default design.

    I suggest you submit your requirement to Visual Studio User Voice and then share the feedback link here. It can let other communities who has the same requirement to vote and add comments.

    Before that, I recommend you use previous version of Visual Studio, such as VS2010.

    Best Regards,
    Weiwei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 12, 2016 1:28 AM
    Moderator