Compare two databases

  • shilpa.shankar87

    Ten Centuries

    Points: 1024

    Im not able to see any of the scripts given above

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Please note, this thread is 8 years old.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    but , i require that to be worked now. Please provide me solution for that.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    http://www.red-gate.com/products/sql-development/sql-compare/

    http://www.red-gate.com/products/sql-development/sql-data-compare/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    ravishankar.yedoti (8/24/2011)


    but , i require that to be worked now. Please provide me solution for that.

    Next time please start a new thread. You're likelier to get more help faster (more people will see it).

    Also be careful about your wording, that phrase can be interpreted as "do my work now".

    I think this is not what you meant and that english is not your best language. So just keep that in mind for next time ;-).

  • ravishankar.yedoti

    Right there with Babe

    Points: 738

    can u provide me script, i don't want it do be done with tool's

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717429

    I believe that tablediff will do this. It's a free tool from MS, but it goes table by table. You could script the calls to have it check all tables for you

    http://msdn.microsoft.com/en-us/library/ms162843.aspx

    Other than that, you should search around the site. I doubt anyone has a SQL 2000 script handy, as it's an older product and comparison isn't necessarily simple. Easy to make mistakes, and hard to debug. Much easier, and safer, for most companies do spend the $300 or so if they need this.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Steve Jones - SSC Editor (8/24/2011)


    I believe that tablediff will do this. It's a free tool from MS, but it goes table by table. You could script the calls to have it check all tables for you

    http://msdn.microsoft.com/en-us/library/ms162843.aspx

    Other than that, you should search around the site. I doubt anyone has a SQL 2000 script handy, as it's an older product and comparison isn't necessarily simple. Easy to make mistakes, and hard to debug. Much easier, and safer, for most companies do spend the $300 or so if they need this.

    300$ for a tool like that is a total no brainer especially if you plan to release all year around.

    Doing anything close to what that tool does will take you far more than 300$ worth of time. Garanteed.

  • MSBI Learner

    Ten Centuries

    Points: 1192

    Link you've provided isn't working, can u pls provide the correct URL as I am in a need of comparing two DBs..

  • Gail Shaw

    SSC Guru

    Points: 1004474

    Link works just fine here: http://msdn.microsoft.com/en-us/library/ms162843.aspx

    tablediff Utility

    SQL Server 2008 R2

    Seriously though, get RedGate's SQLCompare or SQLDataCompare

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • gprias

    SSC Enthusiast

    Points: 190

    Below script is used to Compare fields and their data on two DB's.

    ALTER procedure [dbo].[DataComparision]

    @Server1 varchar(50),

    @Db1 varchar(50),

    @Server2 varchar(50),

    @Db2 varchar(50)

    AS

    BEGIN

    --Schemadiffer is a table that contains the details of field differed information and differed table information.

    Truncate table datacomparison

    DECLARE @tbl varchar(255)

    DECLARE @sql varchar(max)

    DECLARE @tblList CURSOR

    SET @tblList = CURSOR FOR

    -- Comparision table contains the list of tables thaqt should be used in production loading process.

    select tablename from cmp

    OPEN @tblList

    FETCH NEXT

    FROM @tblList INTO @tbl

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sql='if exists(select 1 from ['+@Server1+'].['+@Db1+'].sys.tables where name='''+@tbl+''')

    if exists(select 1 from ['+@Server2+'].['+@Db2+'].sys.tables where name='''+@tbl+''')

    BEGIN

    insert into datacomparison(TableName,FieldName,HomeDB,HomeServer,RivalDB,RivalServer,Comments)

    select '''+@tbl+''' TableName,name FieldName,'''+@Db1+''' HomeDB,'''+@Server1+''' HomeServer,'''+@Db2+''' RivalDB,'''+@Server2+''' RivalServer,''Exists only in HomeDB'' Comments from ['+@Server1+'].['+@Db1+'].sys.columns

    where object_id in (select object_id from ['+@Server1+'].['+@Db1+'].sys.tables where name='''+@tbl+''') and

    name not in

    (select name from ['+@Server2+'].['+@Db2+'].sys.columns where object_id in

    (select object_id from ['+@Server2+'].['+@Db2+'].sys.tables where name='''+@tbl+'''))

    insert into datacomparison(TableName,FieldName,HomeDB,HomeServer,RivalDB,RivalServer,Comments)

    select '''+@tbl+''' TableName,name FieldName,'''+@Db1+''' HomeDB,'''+@Server1+''' HomeServer,'''+@Db2+''' RivalDB,'''+@Server2+''' RivalServer,''Exists only in RivalDB'' Comments from ['+@Server2+'].['+@Db2+'].sys.columns

    where object_id in (select object_id from ['+@Server2+'].['+@Db2+'].sys.tables where name='''+@tbl+''') and

    name not in

    (select name from ['+@Server1+'].['+@Db1+'].sys.columns where object_id in

    (select object_id from ['+@Server1+'].['+@Db1+'].sys.tables where name='''+@tbl+'''))

    DECLARE @cln varchar(255)

    DECLARE @sqlCln varchar(max)

    DECLARE @clnList CURSOR

    SET @clnList = CURSOR FOR

    select name from ['+@Server1+'].['+@Db1+'].sys.columns

    where object_id in (select object_id from ['+@Server1+'].['+@Db1+'].sys.tables where name='''+@tbl+''')

    and name not in (select FieldName from datacomparison where TableName = '''+@tbl+''' and Comments is not NULL)

    OPEN @clnList

    FETCH NEXT

    FROM @clnList INTO @cln

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @sqlCln = ''insert into datacomparison(TableName,FieldName,HomeDB,HomeServer,HomeCount,RivalDB,RivalServer,RivalCount,ColumnDiffernce)''

    set @sqlCln = @sqlCln + ''Select '''''+@tbl+''''' TableName,''''''+@cln+'''''' FieldName,'''''+@Db1+''''' HomeDB,'''''+@Server1+''''' HomeServer,(select count(*) from ['+@Server1+'].['+@Db1+'].dbo.['+@tbl+']) HomeCount,'''''+@Db2+''''' RivalDB,'''''+@Server2+''''' RivalServer,(select count(*) from ['+@Server2+'].['+@Db2+'].dbo.['+@tbl+']) RivalCount,count(*) ColumnDiffernce from (''

    set @sqlCln = @sqlCln+''select [''+@cln+''] from ['+@Server1+'].['+@Db1+'].dbo.['+@tbl+'] ''

    set @sqlCln = @sqlCln+''except ''

    set @sqlCln = @sqlCln+''select [''+@cln+''] from ['+@Server2+'].['+@Db2+'].dbo.['+@tbl+']) as a ''

    --print @sqlCln

    exec(@sqlCln)

    FETCH NEXT

    FROM @clnList INTO @cln

    END

    CLOSE @clnList

    DEALLOCATE @clnList

    END'

    exec(@sql)

    --print @sql

    PRINT @tbl

    FETCH NEXT

    FROM @tblList INTO @tbl

    END

    CLOSE @tblList

    DEALLOCATE @tblList

    END

    Thanks

    Ganesh

  • yonision

    SSC Eights!

    Points: 973

    This one is the most comprehensive, and also the cheapest (schema+data)

    http://nobhillsoft.com/NHDBCompare.aspx

  • je.maguina

    SSC Rookie

    Points: 49

    Here is a tutorial with a free tool included: http://testools.blogspot.com/2013/01/compare-2-sql-server-databases-very.html

  • nitinuniyal

    SSC Eights!

    Points: 997

    Hi

    This link is not working. Could you please send me this script in gmail please.

    nitinuniyal@gmail.com

    Thanks

    NU

  • kumarmca.upp

    Valued Member

    Points: 67

    Dear friend,

    i need to compare two database, like Production and dev dbs, could you plz share query if you have...thanks in advance

    kumar

Viewing 15 posts - 16 through 30 (of 32 total)

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