Compare two databases

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » General » Compare two databases

28 posts, Page 3 of 3

«««1 2 3

Compare two databases

Rate Topic

Display Mode

Topic Options

Author

Message

ravishankar.yedoti

Posted Wednesday, August 24, 2011 11:03 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 03, 2013 11:34 AM
Points: 20, Visits: 137

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

Post #1164852

Steve Jones - SSC Editor

Posted Wednesday, August 24, 2011 11:16 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1164860

Ninja's_RGR'us

Posted Wednesday, August 24, 2011 3:39 PM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 21,376, Visits: 9,584

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

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.

Post #1165025

MSBI Learner

Posted Monday, March 19, 2012 1:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, December 03, 2012 9:14 PM
Points: 44, Visits: 68

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

Post #1268818

GilaMonster

Posted Monday, March 19, 2012 6:20 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 38,094, Visits: 30,387

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 2008, MVP
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

Post #1268914

gprias

Posted Monday, June 18, 2012 1:57 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 10:33 AM
Points: 4, Visits: 18

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

Post #1317561

yonision

Posted Thursday, September 13, 2012 2:23 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 03, 2013 11:39 AM
Points: 52, Visits: 151

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

http://nobhillsoft.com/NHDBCompare.aspx

Post #1358866

je.maguina

Posted Sunday, January 20, 2013 9:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, January 20, 2013 9:13 AM
Points: 1, Visits: 4

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

Post #1409288

« Prev Topic | Next Topic »

28 posts, Page 3 of 3

«««1 2 3

Permissions