No announcement yet.

Compare MS SQL Server data bases

  • Filter
  • Time
  • Show
Clear All
new posts

  • Aaron

    Directly connecting to DB's is still not something we support. You need to Export to a local file first, then we can compare that export. It looks like you also found this thread, which may have some tools for helping compare the local files:

    Leave a comment:

  • crashnburn
    Did someone make more headway in this direction for comparing DB's - Scheme or Data?

    Leave a comment:

  • Chris
    Sorry, BC doesn't have a mySQL plug-in. To compare data from mySQL, you'll have to dump it to a text file (.sql, .csv, etc), then open it in the Text Compare (.sql) or Data Compare (.csv).

    Leave a comment:

  • jhewitt
    Do you know if there is a mySQL plugin for BC?

    Leave a comment:

  • Gurce
    Another way

    Ok, I've been trying bits and pieces on the web. Freeware tools like DataComparisons and DBComparer weren't helpful to me, since they seemed to focus on the sql-schema and not the data within the tables.

    But perhaps I should try and explain my situation a bit, so that it will clarify what sort of solution I've been looking for.

    I have a database that is getting altered by an application that I am un-familiar with. I would like to learn how this program is altering the database by:
    • taking 'BEFORE' snapshot of all table-data
    • using the program which alters the db
    • take an 'AFTER' snapshot of all table-data
    • Use BC3 to compare the two table-data snapshots

    I thought there at-least ought to be a way to dump this table-data into a text-file and let BeyondCompare compare the text files.

    Yeah, this web-site had a good example of that:

    It iterates through each table in your database. I ran into a few error messages with it initially, such as:

    Msg 15281, Level 16, State 1, Server GURCESGCDEV, Procedure xp_cmdshell, Line 1
    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshe
    ll' because this component is turned off as part of the security configuration f
    or this server. A system administrator can enable the use of 'xp_cmdshell' by us
    ing sp_configure. For more information about enabling 'xp_cmdshell', see "Surfac
    e Area Configuration" in SQL Server Books Online.
    Ok, this web-site explained how to fix this:

    Basically, I ran SQLCMD from a console and typed in the suggested lines:
    C:\Documents and Settings\vets>sqlcmd
    1> Exec Master.dbo.Sp_Configure 'Show Advanced Options',1
    2> Reconfigure
    3> Exec Master.dbo.Sp_Configure 'XP_CmdShell',1
    4> Reconfigure
    5> GO
    The script would then execute, but I didn't see any text files outputted. It turned out I needed an extra "-T" parameter in the 'bcp' call.

    Then it worked, I saw lots of text files dumped into a hard-coded "C:\data" path. Yeah, that's ok, but I want to pass a subfolder-parameter into this table-dumping script, so that I can have my 'BEFORE' and 'AFTER' sub-folders to compare with BC3. So I made some modifications to the sql-script on that web-site and I've got the following:

    USE SMGC    /* <------- !!!!REPLACE THIS WITH YOUR DB NAME!!!!! */
    declare @tables table(table_name varchar(100))
    insert into @tables
    select name from sysobjects where xtype ='u'
    declare @table_name varchar(1000)
    select @table_name=min(table_name)from @tables
    Declare @str varchar(1000)
    set @str = 'Exec Master..xp_Cmdshell ''md $(folder)'''
    SELECT @str
     while @table_name>''
         set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table_name+'" queryout "$(folder)\'+@table_name+'.txt" -T -c'''
         SELECT @str
    And now this means I can do the following:
    • sqlcmd -i dump.sql -v folder="%CD%\dump_folder_1_before"
    • run the program which alters the database
    • sqlcmd -i dump.sql -v folder="%CD%\dump_folder_2_after"
    • Compare "dump_folder_1_before" to "dump_folder_2_after" with Beyond Compare and see what has changed.

    Yippee! That works, I'm content with this way But then again, if anyone knows of a better way, I'd be glad to hear it.

    As an extra convenience, I've made a batch file that requests the dump sub-folder name from the user, and then executes the sqlcmd statement for you.

    ECHO Type in a dump-directory name:
    > usermessage.vbs ECHO WScript.Echo InputBox("Dump tables to sub-folder name?", "Dump sub-folder name", "dump1")
    FOR /F "tokens=*" %%A IN ('CSCRIPT.EXE //NoLogo usermessage.vbs') DO SET FOLDER=%%A
    DEL usermessage.vbs
    IF "%FOLDER%"=="" GOTO END
    @ECHO ON
    sqlcmd -i dump.sql -v folder="%CD%\%FOLDER%"
    So now it's just a matter of:
    • dbl-click the "dump.bat" file in explorer
    • In the pop-up inputbox that appears, type the dump sub-folder name
    • the batch file with then call sqlcmd to dump all the tables into your desired sub-folder

    PS. My thanks to these web-pages for helping me figure out the intricacies of batch file input and 'if' statements:
    Last edited by Gurce; 27-Jan-2012, 06:21 PM.

    Leave a comment:

  • Gurce
    This looks really handy

    Hi Knut,

    Your plug-in tool for BC3 looks really handy. I want to give it a try, but I'm unfamiliar with ruby/gem. Is it a big learning curve for me to comprehend it and get this working?

    Or perhaps is the .exe file you created independant of ruby, and I just need that?

    Well, I'll keep reading and tinkering, maybe I'll figure it out, but if you happen to read this and have any tips, I'd be happy to hear it.


    Leave a comment:

  • knut

    I created in meantime a windows exe-file. You can use this exe as a converter.

    The exe worked for me with sqlite (ado is not tested up to now). I'm not sure if it will work without a ruby/sequel installation, but I hope it will

    Leave a comment:

  • knut
    Originally posted by Michael Bulgrien View Post
    Beyond Compare has never had the ability to connect to a SQL Server database and compare live SQL Server objects.
    I found a way to compare live SQL Server objects/datasets.

    The main idea is simple:
    You can define file formats to compare files after a conversion.
    You need only a 'conversion' doing a database selection and store the result in a file.

    I added a ruby solution in my gem bc3 (version 0.2.0).
    To use it, you need ruby with gems bc3, log4r and sequel. The example and unit test requires also sqlite.

    The gem contains file format definitions to use the tool (bc3/examples_db_connect/BCSettings_db4bc3.bcpkg).

    The files to compare contains a definition of the database connection and selection (and of the result you want).

    See the example in the screenshot. The files to compare looks like this:
      adapter: sqlite
      db: ./test.db
      tab: languages
      show_sql: true
      data: csv_tab
      headline: true
        - :key: [ 1, 2, 3, 4, 5, 6, 7, 8 ]
    (the 2nd configuration file does not contain the filter).

    More details at ( deeplink: here and here)

    One little feature request: Could you add an option in "file format->conversions" to set the execution path? (Default BC3-folder, optional another fixed path or source-file-path)

    Leave a comment:

  • Aaron

    In addition to Michael's comments that SQL is a built in File Format, BC3's trial is fully featured with only the days-of-use time limit. You do not need to uninstall BC2. That way you can trial BC3 while still keeping BC2 on your system for day to day use.

    Please let us know if you have any questions.

    Leave a comment:

  • Michael Bulgrien
    Beyond Compare has never had the ability to connect to a SQL Server database and compare live SQL Server objects. A SQL plug-in is available to enable BC2 to compare exported SQL server objects (SQL scripts exported to text files). BC3 comes with built-in file formats including the ability to compare SQL Scripts without the need for an additional file format plug-in.

    Leave a comment:

  • knut
    started a topic Compare MS SQL Server data bases

    Compare MS SQL Server data bases


    actually I have a problem and it would help me, if I could compare the content of two databases (MS SQL Server, tables with same name and structure - I hope )

    For BC2 I detetced "{url=]Rules That Use External Conversion[/url]" include MS SQL Server Object/MS SQL Server SELECT.

    Is there something similar for BC3?