locked
comparing two sql server databases for synchronization RRS feed

  • Question

  • User-153404742 posted

    Hi,

    I'm looking to see if there's a built in way in sql server to write a script that I want run each night to make sure that data schema including tables, stored procedures, functions, etc are the same.  I have first database that will be the up to date one and I want to compare several other databases to match the design of the first one with the exception of certain stored procedures that start with a certain name to be excluded in comparison.  What's the best way to accomplish this?  THanks.

    Wednesday, January 10, 2018 6:29 PM

All replies

  • User753101303 posted

    Hi,

    I believe you should be able to use https://docs.microsoft.com/en-us/sql/relational-databases/data-tier-applications/data-tier-applications

    You also have a schema / data comparison tool in VS 2017 Community.

    Edit: try perhaps also https://blogs.msdn.microsoft.com/ssdt/2016/09/20/schema-compare-in-sqlpackage-and-the-data-tier-application-framework-dacfx/

    Wednesday, January 10, 2018 7:09 PM
  • User347430248 posted

    Hi inkaln,

    you had mentioned that you want to compare two database.

    you can try to use SSDT.

    SQL Server Data Tools (SSDT) includes a Schema Compare utility that you can use to compare two database definitions. The source and target of the comparison can be any combination of connected database, SQL Server database project or snapshot or .dacpac file. The results of the comparison appear as a set of actions that must be taken with the target to make it the same as the source. Once the comparison is complete you can update the target directly (if the target is a project or a database) or generate an update script that has the same effect.

    The differences between source and target appear in a grid for easy review. You can drill into and review each difference in the results grid or in script form. You can then selectively exclude specific differences.

    You can save comparisons either as part of a SQL Server Database project or as a standalone file. You can also set options that control the scope of the comparison and aspects of the update. Then you can save the comparison so that you can easily repeat the same comparison later or use it as the starting point for new comparison.

    Reference:

    How to: Use Schema Compare to Compare Different Database Definitions

    if you want to compare Tables and Data then below links may help you.

    Compare two databases for objects differences

    Ways to compare and find differences for SQL Server tables and data

    Regards

    Deepak

    Thursday, January 11, 2018 2:23 AM
  • User-153404742 posted

    I have used it in visual studio but have to select each target to compare with the source db....I am looking into automating with possible the scmp file from sources db but program a script where I compare several target DBs with the script file, excluding some of the stored procedures and objects.  I'm guessing this is doable in visual studio.  Thanks for the information above.

    Thursday, January 11, 2018 6:43 PM
  • User347430248 posted

    Hi  inkaln,

    Did you try the script that mentioned in the link which i posted in my previous reply?

    If that not work for you then you may need to try some third party tools which can fulfil your requirement.

    Reference:

    Compare SQL Server database schemas automatically

    SQL Server Database Comparison Tool

    Let us know about current status of your issue.

    Regards

    Deepak

    Saturday, January 20, 2018 6:11 AM