Compare two databases

  • I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me?

    I want to compare following:

    Table name in Development and Test databases

    Table structure in Development and Test databases (including identity seed and identity increment in each table)

    View name in Development and Test databases

    View structure in Development and Test databases

    Stored procedure name in Development and Test databases

    Stored procedure structure in Development and Test databases

    We have ‘system data’ tables they hold data, which we need in our VB6 system.

    These tables can be identify as prefix ‘sysd’ and table name e.g. sysdStatus hold all possible statuses for a policy.

    I need to compare those system data in Development and Test databases as well.

    Thank you

  • quote:


    I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me?


    We use AdeptSQL's SQLDiff tool for the database structures, and it's not overly expensive. They also do a tool to compare data within tables.

    http://www.adeptsql.com

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • You can write a script that makes select statements from the information_schema views and do a windiff between the results.

    Something like:

    select * from information_schema.columns order by table_schema,table_name, column_name

    select * from information_schema.views order by table_schema, table_name

    select routine_schema, routine_name, routine_definition from information_schema.routines order by routine_schema,routine_name

  • We use Embarcadero Change Manager. Great tool!

  • Here's a nice freeware tool:

    http://www.davidemauri.it/dabcos/default.aspx

  • I too faced your situation and finally wrote the scripts myself.

    I'll be happy if my scripts are usefull

    Please check

    http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=1771

    There are many wonderfull tools available in the market. But for my need, any where I can control, scripts are the best.

    Cheers,

    Preethiviraj Kulasingham

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • hi!

    concerning own scripts, you could maybe use the following as a starting point:

    http://www.sqlservercentral.com/scripts/contributions/246.asp

    best regards,

    chris.

  • Two other products in the space that I know of, one from Lockwood, one from Red Gate (along with Adept, all three advertise with us!).

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • I've found Viktor Gorodnichenko's sp_CompareDB stored procedure to be very helpful.

    http://www.sql-server-performance.com/viktor_gorodnichenko.asp

  • We had had great success using Red-Gate comparison tools.

    http://www.red-gate.com/sql/summary.htm

    Very affordable and can product a script to syncronize in either direction.

  • Hi Viktor,

    I have tried ur URL but it seems that URL is not active now. Will u please send the script of ur Sp_compare DB database....

  • G.R.Preethiviraj Kulasingham (9/25/2003)


    I too faced your situation and finally wrote the scripts myself.

    I'll be happy if my scripts are usefull

    Please check

    http://www.sqlservercentral.com/scripts/listscriptsbyauthor.asp?author=1771

    There are many wonderfull tools available in the market. But for my need, any where I can control, scripts are the best.

    Cheers,

    Preethiviraj Kulasingham

    The link doesn't work.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Khalidhussain (9/24/2003)


    I need to compare two databases (e.g. Development DB and Test DB) to make sure they are 100% identical. Is there a way to create a SQL view, which can do this job for me?

    I want to compare following:

    Table name in Development and Test databases

    Table structure in Development and Test databases (including identity seed and identity increment in each table)

    View name in Development and Test databases

    View structure in Development and Test databases

    Stored procedure name in Development and Test databases

    Stored procedure structure in Development and Test databases

    We have ‘system data’ tables they hold data, which we need in our VB6 system.

    These tables can be identify as prefix ‘sysd’ and table name e.g. sysdStatus hold all possible statuses for a policy.

    I need to compare those system data in Development and Test databases as well.

    Thank you

    I don't see anything where you talk about what you would do if they're different. It you want to make sure they are the same, snapshot one over the other.

    Now, if you want to identify the differences, then you'll need something like SQL Compare and Data Compare from Redgate. Yes, you could write your own... it will cost you more to do that both in the form of errors and total hours/dolarrs spent than just buying the product which has been tested and tested and retested.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Khalidhussain,

    Instead of wasting your time in writing a script for comparing database objects, I'll suggest to to better go for third party tool available.

    Abhijit - http://abhijitmore.wordpress.com

  • I know you mention that your systems are VB6, but if you also do development on later versions (2005 onwards) of Visual Studio (I think it has to be VS Team System), these have a schema compare tool available, which works after a fashion.

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply