Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Compare two databases Expand / Collapse
Author
Message
Posted Wednesday, August 24, 2011 11:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 27, 2014 11:06 AM
Points: 20, Visits: 163
can u provide me script, i don't want it do be done with tool's
Post #1164852
Posted Wednesday, August 24, 2011 11:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:35 PM
Points: 33,099, Visits: 15,207
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
Posted Wednesday, August 24, 2011 3:39 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 11:14 AM
Points: 21,385, Visits: 9,603
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.
Post #1165025
Posted Monday, March 19, 2012 1:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 6, 2013 2:19 PM
Points: 46, Visits: 70
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
Posted Monday, March 19, 2012 6:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:12 PM
Points: 42,481, Visits: 35,550
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
Posted Monday, June 18, 2012 1:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, September 13, 2012 2:23 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 4:59 PM
Points: 52, Visits: 155
This one is the most comprehensive, and also the cheapest (schema+data)

http://nobhillsoft.com/NHDBCompare.aspx



Post #1358866
Posted Sunday, January 20, 2013 9:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, July 8, 2013 3:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, November 18, 2013 12:57 AM
Points: 25, Visits: 92
Hi

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

Thanks
NU
Post #1471093
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse